CODEWITHSUNDEEP
sqlsqlite
ppusapati
ppusapati

Reputation: 55

Sqlite Query to retrieve distinct values with 0 in null fields

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

Answers (1)

forpas
forpas

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

Related Questions