I want to find the sum of the counts of tables in SQL

Question

Please find below the Query i use

select cm.city_name,

count(case when k.listing_status_uid=1 then 1 end ) as 'Active',   
count(case when k.listing_status_uid=3 then 1 end ) as 'Bad_listing',  
count(case when k.listing_status_uid=4 then 1 end ) as 'proj_scrapped',  
count(case when k.listing_status_uid=5 then 1 end ) as 'proj_on_hold',  
count(case when k.listing_status_uid=6 then 1 end ) as 'sold_out',  
count(case when k.listing_status_uid=7 then 1 end ) as 'others'

from ksl_listing_master k  
join ksl_locality_master lm on lm.locality_uid=k.listing_locality  
join ksl_city_master cm on cm.city_uid=lm.city_uid  
join ksl_listing_status ls on ls.listing_status_uid=k.listing_status_uid  

group by cm.city_name

This gives an output like the below one

Now I want the sum of each individual column. Below the city names Sum should be there I want the individual sum of Active, Bad_listing, proj_scrapped, proj_on_hold, sold_out, and others in the same Query.

Answer 1

If I understand correctly, you just want a summary row. You can do this using with rollup:

select . . .
group by cm.city_name with rollup;