如何按组ID和类别求和

huangapple go评论64阅读模式
英文:

How to Sum by groups id and cat

问题

以下是您要翻译的内容:

我有一个表格
~~~
用户名     计数     类别
Jo            1         A
Jo            5         B
Jo            1         C
An            2         A
An            3         B
An            2         C
~~~
我的目标是按用户名汇总,并且当类别为A和B时进行求和,输出如下所示
~~~
用户名     类别总计     
Jo            6
Jo            1
An            5
An            2
~~~
我的查询
~~~
select 用户名, sum(case when 类别 ='A' and 类别 ='B' then 计数 else 计数 end ) as 类别总计
FROM 表A
group by 用户名, 类别
~~~
不确定为什么没有求和,需要支持。谢谢。
英文:

I have a table

username     counts     cat
Jo            1         A
Jo            5         B
Jo            1         C
An            2         A
An            3         B
An            2         C

My goal to get sum by username and when the cat = A and B then sum so the output like this

username     catcount     
Jo            6
Jo            1
An            5
An            2

My query

select username, sum(case when cat ='A' and cat ='B' then counts Else counts end ) as catcount
FROM tableA
group by username, cat

Not sure why not sum and need support here. Thank you.

答案1

得分: 1

使用带有案例表达式的group by,当cat为'A'时返回'B'(或当cat为'B'时返回'A'),这将将A和B视为一个组。

select username,
       sum(counts) counts
from tableA
group by username, case when cat = 'A' then 'B' else cat end
order by username

或者,您可以使用以下方式使用案例:

select username,
       case when cat in ('A', 'B') then 'AB' else cat end cat,
       sum(counts) counts
from tableA
group by username, case when cat in ('A', 'B') then 'AB' else cat end
order by username

demo

英文:

Use group by with a case expression that returns 'B' when the cat is 'A' (or 'A' when the cat is 'B'), this will consider A and B as a single group.

select username,
       sum(counts) counts
from tableA
group by username, case when cat ='A' then 'B' else cat end
order by username

Or you may use the case as the following:

select username,
       case when cat in ('A', 'B') then 'AB' else cat end cat,
       sum(counts) counts
from tableA
group by username, case when cat in ('A', 'B') then 'AB' else cat end
order by username

demo

答案2

得分: 0

以下是翻译好的部分:

这是另一种使用子选择的解决方案,即在选择语句内部使用选择语句。

内部选择语句将“cat”列的值更改为“AB”或“C”。这是主要操作。

外部选择是对“Group By”的简单应用。

从(选择用户名,总计数作为catcountcat
      tableA
      其中猫= 'A'或猫= 'B'
      联合
      选择用户名,总数,猫
      tableA
      其中不是(猫= 'A'或猫= 'B'
      Temp
按用户名、猫分组;
英文:

Here's another solution that uses sub-Select, i.e. a Select statement inside a Select statement.

The inner Select statement changes the value of the 'cat' column to either 'AB' or 'C'. This is the heavy lifting.

The outer Select is a trivial application on Group By.

Select username, Sum(counts) as catcount, cat
From (Select username, counts, 'AB' cat
      From tableA
      Where cat = 'A' or cat = 'B'
      Union
      Select username, counts, cat
      From tableA
      Where Not (cat = 'A' or cat = 'B')
      ) Temp
Group By username, cat;

huangapple
  • 本文由 发表于 2023年6月9日 08:22:52
  • 转载请务必保留本文链接:https://go.coder-hub.com/76436460.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定