Zephyr Zephyr - 23 days ago 10
SQL Question

SQLite Combine Multiple Queries With Shared Column

I am trying to write a SQLite query that gets the total occurrences of a value, divided by months.

An example of the desired output:

Name Jun Jul Aug Sept
John 3 2 5 2
Robert 4 12 45 22
Linda 0 2 1 0


I want the
Name
field to only return unique results, so it will basically count every time "John" appeared in the month of Jun, Jul, etc.

Here is what I have so far, using my actual DB. It works to return the right columns and unique "ReasonDetail" but the counts are not accurate:

SELECT * FROM

(
SELECT ReasonDetail AS Appetite, COUNT(*) AS June
FROM CallRecords
WHERE CallReason = 'Appetite Question'
AND DateTime BETWEEN '2017-06-01 00:00' AND '2017-07-01 00:00'
GROUP BY ReasonDetail
),
(
SELECT COUNT(*) AS July
FROM CallRecords
WHERE CallReason = 'Appetite Question'
AND DateTime BETWEEN '2017-07-01 00:00' AND '2017-08-01 00:00'
GROUP BY ReasonDetail
),
(
SELECT COUNT(*) AS August
FROM CallRecords
WHERE CallReason = 'Appetite Question'
AND DateTime BETWEEN '2017-08-01 00:00' AND '2017-09-01 00:00'
GROUP BY ReasonDetail
)
GROUP BY Appetite


What am I doing wrong here? Is there a better way to compile the proper counts in this situation?

Thank you!

Answer Source

This is called conditional aggregation. Use case expressions in an aggregate function.

SELECT ReasonDetail AS Appetite
,COUNT(CASE WHEN DateTime>='2017-06-01 00:00' AND DateTime<'2017-07-01 00:00' THEN 1 END) AS June
,COUNT(CASE WHEN DateTime>='2017-07-01 00:00' AND DateTime<'2017-08-01 00:00' THEN 1 END) AS July
,COUNT(CASE WHEN DateTime>='2017-08-01 00:00' AND DateTime<'2017-09-01 00:00' THEN 1 END) AS August
FROM CallRecords
WHERE CallReason = 'Appetite Question'
GROUP BY ReasonDetail