killboy Posted March 31, 2009 Share Posted March 31, 2009 Hi there.Is there any way of doing something like SELECT * FROM myTableWHERESUM ( colA - colB ) > 0 ???Thanks for the help. Link to comment Share on other sites More sharing options...
justsomeguy Posted March 31, 2009 Share Posted March 31, 2009 It doesn't make much sense to do that. The sum function is an aggregate function, meaning that it operates on several rows. I don't think you can use sum on a difference between columns, but maybe you can. Either way, that query would select all rows from the table if the sum of the differences between the columns was greater than zero. So it looks like it's either going to select all rows or no rows. Link to comment Share on other sites More sharing options...
killboy Posted March 31, 2009 Author Share Posted March 31, 2009 Well, I wanted to keep it simple, but in the real query, I'm bringing the data from several tables. I have several INNER JOINs.The thing is, I need to compare the column of one table vs. SUM( another table's column ). If the sum of these two columns is greater than zero, it will bring the data.I already tried the WHERE SUM ( columnA - columnB ) and I got an error. That's why I want to know if there's a way to do something similiar.Thanks for the replies. Link to comment Share on other sites More sharing options...
killboy Posted March 31, 2009 Author Share Posted March 31, 2009 OK, this is the real query: SELECTmos_ap_member_company.id,mos_ap_member_company.name,mos_ap_contact.first_name AS contact_first_name,mos_ap_contact.last_name AS contact_last_name,mos_ap_member_billing.billing_month,mos_ap_member_billing.billing_day,SUM( mos_ap_payment_billing.amount ) AS totalFROMmos_ap_member_companyINNER JOINmos_ap_member_billingONmos_ap_member_company.id = mos_ap_member_billing.id_member_companyINNER JOINmos_ap_contactONmos_ap_member_billing.id_primary_contact = mos_ap_contact.idINNER JOINmos_ap_service_headONmos_ap_member_company.id = mos_ap_service_head.id_member_companyINNER JOINmos_ap_payment_billingONmos_ap_service_head.billing_num = mos_ap_payment_billing.billing_numWHERE( mos_ap_service_head.total - SUM( mos_ap_payment_billing.amount ) ) > 0GROUP BYmos_ap_member_company.id ASC Link to comment Share on other sites More sharing options...
justsomeguy Posted March 31, 2009 Share Posted March 31, 2009 What does the error message say? Link to comment Share on other sites More sharing options...
justsomeguy Posted March 31, 2009 Share Posted March 31, 2009 dup Link to comment Share on other sites More sharing options...
killboy Posted April 2, 2009 Author Share Posted April 2, 2009 It says I can't use an aggregate function in WHERE clause..Nevermind though, I solved it.I replaced the WHERE( mos_ap_service_head.total - SUM( mos_ap_payment_billing.amount ) ) > 0 for nested query: WHERE( mos_ap_service_head.total - ( SELECT SUM( mos_ap_payment_billing.amount ) FROM [table] WHERE ............ )) > 0 Thanks for the help though. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.