I'm getting unexpected results when I use SUM(CASE WHEN .... THEN "column_name") in sqlite3

Question

I am trying to find the number of orders I got in the month of April. I have 3 orders but my query gets the result 0. What could be the problem?

Here's the table:

id | first   | middle | last    | product_name  | numberOut | Date
1 | Muhammad | Sameer | Khan    | Macbook       | 1         | 2020-04-01
2 | Chand    | Shah   | Khurram | Dell Optiplex | 1         | 2020-04-02
3 | Sultan   |        | Chohan  | HP EliteBook  | 1         | 2020-03-31
4 | Express  | Eva    | Plant   | Dell Optiplex | 1         | 2020-03-11
5 | Rana     | Faryad | Ali     | HP EliteBook  | 1         | 2020-04-02

And here's the query:

SELECT SUM(CASE WHEN strftime('%m', oDate) = '04' THEN 'id' END) FROM orders;

Answer 1

If you want all Aprils, then you can just look at the month. I would recommend:

select count(*)
from orders o
where o.date >= '2020-04-01' and o.date < '2020-05-01';

Note that this does direct comparisons of date to a valid dates in the where clause.

Answer 2

The problem with your code is this:

THEN 'id'

You are using the aggregate function SUM() and you sum over a string literal like 'id' which is implicitly converted to 0 (because it can't be converted to a number) so the result is 0.
Even if you remove the single quotes you will not get the result that you want because you will get the sum of the ids.
But if you used:

THEN 1 ELSE 0

then you would get the correct result.
But with SQLite you can write it simpler:

SELECT SUM(strftime('%m', oDate) = '04') FROM orders;

without the CASE expression.
Or since you just want to count the orders then COUNT() will do it:

SELECT COUNT(*) FROM orders WHERE strftime('%m', oDate) = '04';

Edit.
If you want to count the orders for all the months then group by month:

SELECT strftime('%Y-%m', oDate) AS month, 
       COUNT(*) AS number_of_orders
FROM orders 
GROUP BY month;

Answer 3

SELECT SUM(CASE WHEN strftime('%m', oDate) = '04' THEN 1 ELSE 0 END) FROM orders; 

if you need to use SUM

Answer 4

There is a problem with your query. You do not need to do that aggregation operation.

SELECT COUNT(*) from table_name WHERE strftime('%m', Date) = '04';

Answer 5

I would use explicit date comparisons rather than date functions - this makes the query SARGeable, ie it may benefit an existing index.

The most efficient approach, with a filter in the where clause:

select count(*) cnt
from orders 
where oDate >= '2020-04-01' and oDate < '2020-05-01'

Alternatively, if you want a result of 0 even when there are no orders in April you can do conditional aggregation, as you originally intended:

select sum(case when oDate >= '2020-04-01' and oDate < '2020-05-01' then 1 else 0 end) cnt
from orders