Invalid use of group function SUM() in WHERE

Question

I am having a problem with my query.

I want to use SUM in my WHERE but it keeps giving me Invalid use of group function.

AND SUM((invoices_out.net+invoices_out.vat)/currencies.rateVsPound)
>
(SELECT SUM(jobs_payments_accounts.amount/currencies.rateVsPound)
 FROM jobs_payments_accounts, jobs, enquiries, currencies
 WHERE jobs_payments_accounts.jobRef = jobs.id
 AND jobs_payments_accounts.currencyRef = currencies.id
 AND enquiries.id = jobs.enquiryRef
 AND enquiries.entityRef = ed.id)

The right side of > works perfectly, but how can I achieve what I'm trying to do with the left side?

Here is the full subquery:

IF(ed.paymentTermsRef = 3,
(SELECT SUM((invoices_out.net+invoices_out.vat)/currencies.rateVsPound)
FROM enquiries, jobs, (SELECT * FROM invoices_out_reference GROUP BY invoiceRef) AS iorUnique,
                                (SELECT enquiryRef, sellingCurrency FROM parts_trading GROUP BY enquiryRef) AS PTU,
                                currencies,
                                invoices_out
                                    WHERE enquiries.entityRef = ed.id
                                    AND enquiries.id = jobs.enquiryRef
                                    AND PTU.enquiryRef = enquiries.id
                                    AND PTU.sellingCurrency = currencies.id
                                    AND jobs.id = iorUnique.jobRef
                                    AND iorUnique.invoiceRef = invoices_out.id
                                    AND invoices_out.paid = 0
                                AND SUM((invoices_out.net+invoices_out.vat)/currencies.rateVsPound)
                                    >
                                    (SELECT SUM(jobs_payments_accounts.amount/currencies.rateVsPound)
                                         FROM jobs_payments_accounts, jobs, enquiries, currencies
                                         WHERE jobs_payments_accounts.jobRef = jobs.id
                                         AND jobs_payments_accounts.currencyRef = currencies.id
                                         AND enquiries.id = jobs.enquiryRef
                                         AND enquiries.entityRef = ed.id)
                                #AND jobs.id NOT IN
                                #       (SELECT jobRef FROM jobs_payments_accounts)
                                #   AND jobs.id NOT IN
                                #       (SELECT jobRef FROM jobs_payments_advance)
                                AND jobs.id IN
                                    (SELECT jobRef FROM invoices_out_reference)
                                AND DATEDIFF(NOW(), invoices_out.date) >= 30),
                                NULL
                             )

Tried implementing what you suggested with IS NULL but it makes the query much slower.

Still having a lot of trouble with this, hopefully I will crack it soon somehow.

In your select if you did "SELECT SUM(jobs_payments_accounts.amount/currencies.rateVsPound) as myval" can you not do where myval > ... (untested) — BugFinder, Jun 22 '12 at 11:09

score 15 · Accepted Answer · edited May 23 '17 at 11:53

The first rule of the "Aggregate functions users club" is:

You don't use aggregate functions in WHERE

The second rule of the "Aggregate functions users club" is:

You don't use aggregate functions in WHERE

HAVING clause has to be used in such cases: here's a great topic to study some intricacies of it.

And I really suggest restructuring your query (why use nested subqueries to check jobs.id in three different tables instead of LEFT JOIN ... ON id IS NULL, for example?) or dividing it into smaller ones with temporary tables. No offence, but when looking at what you've quoted, I actually can hear your DB cries.

score 8 · Answer 2 · answered Jun 22 '12 at 11:11

8

You must use HAVING with aggregate functions:

select ...
from ...
where ...
group by ...
having sum(...) > ...

answered Jun 22 '12 at 11:11

Bohemian

412,405
93
575
722

Invalid use of group function SUM() in WHERE

2 Answers2