CODEWITHSUNDEEP
javaandroidsqlsqlite
Soul
Soul

Reputation: 610

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 tb_journal WHERE range_1 < 36 AND range_2 < 18 ),
LEFT JOIN (SELECT COUNT(*) FROM tb_journal WHERE range_1 > 36 AND range_1 < 100 AND range_2 > 18 AND range_2 < 100 ),
LEFT JOIN (SELECT COUNT(*) FROM tb_journal WHERE range_1 > 100 AND range_2 > 100 )
GROUP BY periodOfDay

I have the following table:

id, range_1, range_2, periodOfDay

periodOfDay is 0, 1, 2

My aim is to get an array with just one query with

Is there a way to realize such a thing with just one query without implementing it via code?

Thank you very much.

Upvotes: 0

Views: 162

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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;

Upvotes: 2

Related Questions