我想在sql中找到表的计数之和

hgtggwj0 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(336)

请在下面找到我使用的查询

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

这会产生如下输出

现在我想要每一列的总和。下面的城市名称总和应该有我想要的个人总和 Active , Bad_listing , proj_scrapped , proj_on_hold , sold_out ，和 others 在同一个查询中。

sql mysql heidisql

来源：https://stackoverflow.com/questions/51599009/i-want-to-find-the-sum-of-the-counts-of-tables-in-sql

2条答案

按热度按时间

rlcwz9us1#

你似乎想要：

select cm.city_name,
       sum(k.listing_status_uid = 1) as 'Active',   
       sum(k.listing_status_uid = 3) as 'Bad_listing',  
       sum(k.listing_status_uid = 4) as 'proj_scrapped',  
       . . .
       sum(k.listing_status_uid in (1,3,4)) as 'total'
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;

赞(0）回复(0）举报 2021-06-20

ddhy6vgd2#

如果我理解正确，您只需要一个摘要行。你可以使用 with rollup :

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

赞(0）回复(0）举报 2021-06-20

我来回答

我想在sql中找到表的计数之和

2条答案

相关问题

热门标签

最新问答