mysql - #1111 - Invalid use of group function -
the following query failing , complaining #1111 - invalid use of group function
because of both sum() calls embedded inside if loops. i'm trying accomplish same goals while migrating sql query i'm having osme hard time, 1 point me right direction?
database composed invoices
table may have invoices_payments
if client hasn't paid invoice @ once , instead have gone instal payments. known that, i'm trying sum invoices.invoice_total substracting each invoices_payments.payment_date whichs not equals current week if invoices.issued_date equals current week, if not i'm trying sum invoices_payments.payment_amount invoices_payments.payment_date equals current week.
select sum( if ( yearweek(i.issue_date, 1) = yearweek(now(), 1), i.invoice_total, 0 ) ) week_invoiced, ##sum functions embedded inside next if causing error: #1111 - invalid use of group function sum( if ( ## if payment issued on current week i.issued_date not null , yearweek(i.issued_date, '%y%m') = yearweek(now(), '%y%m'), ## sum each invoice.invoice_total , substract each invoices_payments not issued on current week sum(i.invoice_total) - sum( if ( ip.id not null , yearweek(ip.payment_date, 1) != yearweek(now(), 1), ip.payment_amount, 0 ) ), if ( ## if payment not issued on current week i.issued_date null or ( i.issued_date not null , yearweek(i.issued_date, '%y%m') != yearweek(now(), '%y%m') ), ## sum each invoices_payments issued on current week sum( if ( ip.id not null , yearweek(ip.payment_date, 1) = yearweek(now(), 1), ip.payment_amount, 0 ) ), 0 ) ) ) week_paid, sum( if ( date_format(i.issue_date, '%y%m') = date_format(now(), '%y%m'), i.invoice_total, 0 ) ) month_invoiced `invoices` inner join `currency` cur on i.currency_id = cur.id left join `invoices_payments` ip on ip.invoice_id = i.id i.coach_id = 279 , ( date_format(i.issue_date, '%y%m') = date_format(now(), '%y%m') or date_format(i.issued_date, '%y%m') = date_format(now(), '%y%m') or ( ip.id not null , date_format(ip.payment_date, '%y%m') = date_format(now(), '%y%m') ) ) group i.id
you don't need outer sum
problem is. is, inner sums suffice:
sum( sum()-sum() )
-->
sum()-sum()
Comments
Post a Comment