SQL 添加两列并按计数分组

Question

我想添加两列并按计数分组。例如，假设我有下表：

ID -------- value1 ---------- value2    
A ------------ 2 -----------------3    
B ------------ 1 -----------------4    
c ------------ 2 -----------------2   
D ------------ 3 -----------------3   
E ------------ 2 -----------------1
F ------------ 1 -----------------3

如果您计算每行的总和(select value1 + value 2)，您将获得 5,5,4,6,3,4。

我想得到以下结果。

5 ------ 2    
4 ------ 2   
3 ------ 1   
6 ------ 1

Answer 1

你可以做：

select (val1 + val2), count(*)
from t
group by (val1 + val2)
order by count(*) desc;

Answer 2

这是一个

解决方案：在CTE中SUM两列，然后只COUNT按总和金额分组的id：

declare @tbl as table (
    id varchar(1)
    ,val1 int
    ,val2 int
)
insert into @tbl values ('A',2,3)
insert into @tbl values ('B',1,4)
insert into @tbl values ('C',2,2)
insert into @tbl values ('D',3,3)
insert into @tbl values ('E',2,1)
insert into @tbl values ('F',1,3)
;WITH CTE AS (
    SELECT
        id
        ,val1+val2 as [sum]
    FROM @tbl
)
SELECT
    [sum]
    ,count(id) as [count]
FROM CTE
GROUP BY sum