MySQL error getting total values between two dates and another filter

Question

I want to get SUM values from a table between two dates but I have another filter also. I am getting syntax error and could not find what is wrong. This is the SQL sentence:

SUM(IF((date BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 23:59:59') AND ql=1),total,0) AS t1q1,
SUM(IF((date BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 23:59:59') AND ql=2),total,0) AS t1q2,
SUM(IF((date BETWEEN '2019-12-01 00:00:00' AND '2019-12-31 23:59:59') AND ql=1),total,0) AS t2q1,
SUM(IF((date BETWEEN '2019-12-01 00:00:00' AND '2019-12-31 23:59:59') AND ql=2),total,0) AS t2k2
FROM myTable 
GROUP BY productID
ORDER BY productID ASC

`SUM(case when date BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 23:59:59' AND ql=1 then total else 0 end) AS t1q1,` — juergen d, Jan 31 '20 at 06:31

score 0 · Accepted Answer · answered Jan 31 '20 at 06:49

0

SELECT
SUM(case when date>='2020-01-01' and date<'2020-02-01' AND ql=1 then total else 0 end) t1q1,
SUM(case when date>='2020-01-01' and date<'2020-02-01' AND ql=2 then total else 0 end) t1q2,
SUM(case when date>='2019-12-01' and date<'2020-01-01' AND ql=1 then total else 0 end) t2q1,
SUM(case when date>='2019-12-01' and date<'2020-01-01' AND ql=2 then total else 0 end) t2q2
FROM myTable 
GROUP BY productID
ORDER BY productID ASC

answered Jan 31 '20 at 06:49

juergen d

201,996
37
293
362

`else 0 ` is excess - SUM() ignores NULL values. And the conditions by `date` may be moved to WHERE. – Akina Jan 31 '20 at 06:56
I know, but I find using `else` better readable for newbies to SQL. Otherwise it is harder to understand what is going on. – juergen d Jan 31 '20 at 07:03
@Akina: And the condition by date can NOT be moved to WHERE – juergen d Jan 31 '20 at 07:14
Oops.. I have mislooked that the month differs. – Akina Jan 31 '20 at 07:17

MySQL error getting total values between two dates and another filter

1 Answers1