我在SQL上摆弄group by子句,并根据临时表"A“的结果找出百分比。
这就是小提琴。
http://sqlfiddle.com/#!9/faf2f/6959
我确实遇到过不同的实现方法,比如使用union all,但我的问题是,为什么这个查询只返回一行,而不是两行。
数据:
TotalCost
230
200
100
1254
查询:
SELECT Category, Cnt , Cnt/sum(Cnt) as percent from (
SELECT
Case When TotalCost < 301 Then '0-300'
Else ' > 300' End as Category,
count(*) as cnt
FROM Cars
group by Category
) A
;
预期结果:
Category Cnt percent
0-300 3 75
> 300 1 25
实际结果:
Category Cnt percent
> 300 1 25
发布于 2018-08-15 05:09:18
case when
而不是别名进行分组。看起来像这样。
SELECT Category, Cnt , Cnt/(select count(*) from Cars) * 100 as percent
from (
SELECT
(Case When TotalCost < 301 Then '0-300'
Else ' > 300' End) as Category,
count(*) as cnt
FROM Cars
GROUP BY (Case When TotalCost < 301 Then '0-300'
Else ' > 300' End)
) A
ORDER BY 3 DESC
或者,您可以使用CROSS JOIN
来获取总数。
SELECT Category, Cnt , Cnt/v.totle * 100 as percent
from (
SELECT
(Case When TotalCost < 301 Then '0-300'
Else ' > 300' End) as Category,
count(*) as cnt
FROM Cars
GROUP BY (Case When TotalCost < 301 Then '0-300'
Else ' > 300' End)
) A CROSS JOIN (select count(*) totle from Cars) v
结果
| Category | Cnt | percent |
|----------|-----|---------|
| 0-300 | 3 | 75 |
| > 300 | 1 | 25 |
https://stackoverflow.com/questions/51849718
复制相似问题