我有数据一行相同的value和id的例子如下:
table_name:测试
id | amount
1 | 100
1 | 100
1 | 100
2 | 150
2 | 150
2 | 150
3 | 200
3 | 200
3 | 200
4 | 250
4 | 250
4 | 250
我想在每个id中只求和一行，使用下面的sql不工作，它求和所有行。
"select *, sum(amount) as total from test group by id";
我的问题是可能的sum()只有一个row每个id ?
期望输出值吗? (编辑)
id | amount
1 | 100
2 | 150
3 | 200
4 | 250
total : 700
我的问题是，可能的sum()只有一行每个id?
我将此解释为您想要一个值，每个组中有一行。一种方法是两层聚合:
select sum(amount)
from (select id, max(amount) as amount
from test
group by id
) t;
看起来你需要这个
select *,sum(amount) from (select distinct * from test) as t group by id
尝试:
select id, sum(amount)/count(*) as total
from test
group by id
| id | total |
|----|-------|
| 1 | 100 |
| 2 | 150 |
| 3 | 200 |
| 4 | 250 |
尝试使用subquery-
select sum(amount) from (select distinct id,amount from company7) as tempTable
试试这个
create table #test
(id int, amount int)
insert into #test values (1,100),(1,100),(1,100),(2,150),(2,150),(3,200),(3,250)
;with cte
as
(
select sum(distinct amount) as damount,id
from #test
group by id
)
select sum(damount) as total from cte
对于除SQL Server以外的其他dbms
select sum(damount) as total from (select sum(distinct amount) as damount,id
from test
group by id) as it