SQL多类别分组

这是我用来统计每类预订的活动数量的查询。

SELECT
  Categories.name,
  count(case when locations.name ='loc 1' then 1 end) as Location1,
  count(case when locations.name ='loc 2' then 1 end) as Location2,
  count(case when locations.name ='loc 3' then 1 end) as Location3,
  count(Categories.name) as total

FROM
  ...

group by Categories.name
with rollup

这给了我以下结果。

+------------------------------------------------------------+
|Category_name   | Location1 | Location2 | Location3 | Total |
+------------------------------------------------------------+
|Cat1            | 1         | 2         | 2         | 5     | 
|Cat2            | 2         | 1         | 2         | 5     | 
|Cat3            | 2         | 2         | 2         | 6     | 
|Cat3(Extra)     | 1         | 3         | 2         | 6     | 
|Cat4            | 3         | 1         | 2         | 6     | 
+------------------------------------------------------------+
|Total per loc   | 9         | 9         | 10        | 28    |
+------------------------------------------------------------+

到现在为止,这很令人满意!现在我需要做同样的请求,但是我想我需要把组别改成(??),有没有办法把我的请求改成这样。

Count for Cat1 the number of events per location.
Count for Cat2 the number of events per location.
Count for Cat3 and Cat3(extra) the number of events per location.
Count for Cat4 the number of events per location.

我的意思是一些Categories需要一起计算,而我想实现的是这个结果。

+------------------------------------------------------------+
|Category_name   | Location1 | Location2 | Location3 | Total |
+------------------------------------------------------------+
|Cat1            | 1         | 2         | 2         | 5     | 
|Cat2            | 2         | 1         | 2         | 5     | 
|Cat3            | 3         | 5         | 4         | 12    | 
|Cat4            | 3         | 1         | 2         | 6     | 
+------------------------------------------------------------+
|Total per loc   | 9         | 9         | 10        | 28    |
+------------------------------------------------------------+

解决方案:

只要包含一个 case 的声明 group by 的表达式。

  SELECT (CASE WHEN Categories.name like 'Cat3%' THEN 'Cat3'
               ELSE Categories.name
          END) as name,
         sum(locations.name = 'loc 1' ) as Location1,
         sum(locations.name = 'loc 2') as Location2,
         sum(locations.name = 'loc 3') as Location3,
         count(*) as total
  FROM ...
  GROUP BY (CASE WHEN Categories.name like 'Cat3%' THEN 'Cat3'
                 ELSE Categories.name
            END)

给TA打赏
共{{data.count}}人
人已打赏
未分类

使用纵向面板数据在ggplot中绘制线图。

2022-9-8 6:47:42

未分类

无法打开JDBC连接oracle和spring boot。

2022-9-8 6:58:36

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索