Reputation: 55
Trying to write a query to retrieve data with distinct months
Expected Result
months12 sev1 sev2
01 1 21
02 1 12
03 0 4
09 1 0
Getting Result
months12 sev1 sev2
01 1 21
01 1 12
01 1 4
02 1 21
02 1 12
02 1 4
03 1 21
03 1 12
03 1 4
09 1 21
09 1 12
09 1 4
Here is the query
select DISTINCT months12, sev1, sev2 from
(select Distinct strftime('%m', date(issue_reported)) months12 from tickets),
(select DISTINCT strftime('%m', date(issue_reported)) months, coalesce(count(*), 0) sev1
from tickets where ticket_severity="Sev 1" and strftime('%m', date(issue_reported)) in (select DISTINCT strftime('%m', date(issue_reported)) months from tickets) Group By ticket_severity, strftime('%m', date(issue_reported))),
(select DISTINCT strftime('%m', date(issue_reported)) months1, coalesce(count(*), 0) sev2
from tickets where ticket_severity="Sev 3" and strftime('%m', date(issue_reported)) in (select DISTINCT strftime('%m', date(issue_reported)) months from tickets) Group By ticket_severity, strftime('%m', date(issue_reported)))
Upvotes: 0
Views: 23
Reputation: 164234
I think that you need conditional aggregation:
select strftime('%m', date(issue_reported)) months12,
sum(ticket_severity = 'Sev 1') sev1,
sum(ticket_severity = 'Sev 2') sev2
from tickets
group by months12
Upvotes: 1