alecam alecam - 7 months ago 11
SQL Question

Display non-resultant row SQL (groupby - count)

I have a problem with a query (MS SQL Server) and I want to know if it is possible to do something like the following and how to do it properly.

This is the query:

select numTenants, count(codSite) numSites
from (select case count(st1.name) when 0 then '0T'
when 1 then '1T'
when 2 then '2T'
when 3 then '3T'
when 4 then '4T'
else 'More than 4T' end numTenants, os1.siteCode as codSite
from fl_OperativeSite os1 left join fl_SiteTenant st1
on st1.fkOperativeSite=os1.pkOperativeSite
where os1.siteType='A' and os1.externalInfrastructure=2
group by os1.siteCode) groups
group by numTenants
order by numTenants


and this is the result:

numTenants numSites
1T 2957
2T 553
3T 1503
4T 423
More than 4T 131


Because obviously there is not a "site" with 0 as numTenants.

What I wanted to ask is: is there a way to have the result shown as below?

numTenants numSites
0T 0
1T 2957
2T 553
3T 1503
4T 423
More than 4T 131


Thank you very much!

Answer

Assuming your own query is working fine. create one table variable.

declare @tbl table(numTenants varchar(50))
insert into @tbl values ('0T'), ('1T'),('2T'),('3T'),('4T'),('More than 4T')

;With CTE As
(
 select numTenants, count(codSite) numSites
from (select case count(st1.name) when 0 then '0T'
                       when 1 then '1T' 
                       when 2 then '2T' 
                       when 3 then '3T'
                       when 4 then '4T'
                       else 'More than 4T' end numTenants, os1.siteCode as codSite
    from fl_OperativeSite os1 left join fl_SiteTenant st1
        on st1.fkOperativeSite=os1.pkOperativeSite
    where  os1.siteType='A' and os1.externalInfrastructure=2
    group by os1.siteCode) groups
group by numTenants
order by numTenants
)

select a.numTenants,isnull(s.numSites,0) numSites from @tbl A 
left join CTE S on a.numTenants=s.numTenants