SQL 如何通过不同的 id 进行条件求和？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5971267/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL How to do a conditional sum by distinct id?
提问by Oscar Gomez
Suppose you have result set such as:
假设您有结果集，例如：
DATE ID cost
---------------------------------------
01/01/2011 1 10
01/01/2011 1 10
01/01/2011 2 10
01/01/2011 2 10
I want a way to sum the values on cost but only once for every distinct ID so that when i group by date I get a result such as
我想要一种方法来对成本值求和，但对每个不同的 ID 只求一次，这样当我按日期分组时，我会得到一个结果，例如
DATE cost
01/01/2011 20
I first tried something like
我首先尝试了类似的东西
sum(distinct cost)
but that of curse only returns 10 I also tried:
但诅咒只返回 10 我也试过：
sum(case when distinct id then cost else 0 end)
but that is not a functional query.
但这不是功能查询。
回答by Adriano Carneiro
I will assume that the same ID will always have the same cost in the same day. I will also assume your RDBMS supports derived tables. In that case, this is what you want:
我将假设相同的 ID 在同一天总是具有相同的成本。我还将假设您的 RDBMS 支持派生表。在这种情况下，这就是您想要的：
select date, sum(cost)
from
(select distinct date, id, cost from YourTable)
group by date
Updated
更新
Oracle derived tables do not require alias.
Oracle 派生表不需要别名。
回答by Andrew Kuklewicz
I think all you want to do here is group by both date and id, as MPelletier wrote.
正如 MPelletier 所写，我认为您在这里要做的就是按日期和 ID 分组。
FWIW - you can do a distinct inside an aggregate in some dbs: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_sum
FWIW - 您可以在某些 dbs 中的聚合内部进行不同的处理：http: //dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_sum
And even better, use group by with rollup to get totals along with the grouping: http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html
更好的是，使用 group by 和 rollup 来获得总数和分组：http: //dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html
SELECT DATE, ID, SUM(cost)
FROM table_name
GROUP BY DATE, ID WITH ROLLUP;
回答by ypercube??
I think there is some flaw with the logic of the posted question.
我认为发布的问题的逻辑存在一些缺陷。
Suppose you have this result set:
假设你有这个结果集：
DATE ID cost
---------------------------------------
01/01/2011 1 10
01/01/2011 1 15
01/01/2011 2 10
01/01/2011 2 10
What should the query return?
查询应该返回什么？
DATE cost
---------------------------------------
01/01/2011 20
or
或者
DATE cost
---------------------------------------
01/01/2011 25
The accepted answer will return 35.
接受的答案将返回35。
Or is such a set impossible to have?
或者这样的一套是不可能有的？
回答by Davinder Kumar
You can use this query.
您可以使用此查询。
SELECT dates, Sum(cost) as TCost FROM TABLE_NAME GROUP BY ID
回答by MPelletier
Without knowing the database/SQL engine:
在不知道数据库/SQL 引擎的情况下：
SELECT Date, Sum(Cost)
FROM YourTable
GROUP BY Date, ID;
SELECT Date, Sum(Cost) FROM YourTable GROUP BY Date, ID;
SELECT Date, First(TCost)
FROM
(SELECT Date, Sum(Cost) as TCost FROM YourTable GROUP BY Date, ID)
GROUP BY Date
回答by AlMounkez
TRY THIS
尝试这个
SELECT SUM(cost) AS Expr1
, ID
, date
FROM (SELECT DISTINCT date
, ID
, cost
FROM table_name AS table_name_1
GROUP BY date, ID, cost) AS derivedtbl_1
GROUP BY ID, date
or THIS
或这个
SELECT SUM(cost) AS Expr1, date
FROM ( SELECT DISTINCT date, ID, cost
FROM table_name AS table_name_1
GROUP BY date, ID, cost) AS derivedtbl_1
GROUP BY date
回答by Naisarg Parmar
Try This
I am using Mysql for this.
DEMO TABLE(Table name is 'tablename'. id and cost is type of 'int' )
试试这个
我为此使用了 Mysql。DEMO TABLE（表名是'tablename'。id 和cost 是'int' 的类型）
id | cost
1 | 15
1 | 25
1 | 10
2 | 25
2 | 5
2 | 10
3 | 20
3 | 15
3 | 10
4 | 20
4 | 20
Query use
查询用途
select id
, SUM(cost) as 'Total Cost'
from sumchk
group by id
OR (use below one)
或（使用以下之一）
select id, sum(cost) as 'Total Cost'
from (select *
from tablename
) a
group by id;
Result :
结果 ：
id | Total Cost
1 | 50
2 | 40
3 | 45
4 | 40
