Invalid use of group function SUM() in WHERE
Solution 1
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.
Solution 2
You must use HAVING
with aggregate functions:
select ...
from ...
where ...
group by ...
having sum(...) > ...
Comments
-
imperium2335 almost 2 years
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.