Where Sum ( Column )

[killboy]

Hi there.Is there any way of doing something like

SELECT * FROM myTableWHERESUM ( colA - colB ) > 0

???Thanks for the help.

[justsomeguy]

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.

[killboy]

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.

[killboy]

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

[justsomeguy]

What does the error message say?

[justsomeguy]

dup

[killboy]

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.