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.