CODEWITHSUNDEEP
sql-server
sqlhelp
sqlhelp

Reputation: 11

Want to force zero in group by clause

Hi here is my simple query

--Code formatting
select severity as measurement,
       count(tkts) as tkts
from Maintable
where (Bunch of conditions)
group by severity

I am getting correct result but it is not showing the severity where result is 0, I want to force all severity even if they had zero tickets.

Using SQL server 2012

Upvotes: 1

Views: 42

Answers (1)

Andomar
Andomar

Reputation: 238086

Start with all severity values, and left join the main table:

select  sev.severity as measurement
,       count(mt.id) as tkts 
from    (
        values (1), (2), (3), (4), ..., (N)
        ) sev(severity)
left join
        (
        select  *
        from    Maintable 
        where   <bunch of conditions>
        ) mt
on      mt.severity = sev.severity
group by 
        sev.severity

Per your comment, to add a tier:

select  sev.severity as severity_name
,       tier.tier as tier_name
,       count(mt.id) as tkts 
from    (
        values (1), (2), (3), (4), ..., (N)
        ) sev(severity)
cross join
        (
        values ('A'), ('A2'), ('B'), ..., ('last')
        ) tier(tier)
left join
        (
        select  *
        from    Maintable 
        where   <bunch of conditions>
        ) mt
on      mt.severity = sev.severity
        and mt.iter = tier.tier
group by 
        sev.severity
,       tier.tier

Upvotes: 2

Related Questions