Sqlite: Nested Select With Conditions

I am not very expert with SQLite, I searched but nothing came to help me. The query is wrong, but the logic is what I would need: SELECT COUNT(*), LEFT JOIN (SELECT COUNT(*) FROM t

Solution 1:

Use conditional aggregation -- that is, use CASE as an argument to the SUM():

SELECT periodOfDay, COUNT(*),
       SUM(CASE WHEN range_1 < 36 AND range_2 < 18 THEN 1 ELSE 0 END),
       SUM(CASE WHEN range_1 > 36 AND range_1 < 100 AND range_2 > 18 AND range_2 < 100 THEN 1 ELSE 0 END),
       SUM(CASE WHEN range_1 > 100 AND range_2 > 100 THEN 1 ELSE 0 END)
FROM tb_journal
GROUP BY periodOfDay;