oracle 如何在oracle中同时使用distinct和sum？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30864479/
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
How to use distinct and sum both together in oracle?
提问by sk1007
For example my table contains the following data:
例如我的表包含以下数据：
ID price
-------------
1 10
1 10
1 20
2 20
2 20
3 30
3 30
4 5
4 5
4 15
So given the example above,
所以给出上面的例子，
ID price
-------------
1 30
2 20
3 30
4 20
-----------
ID 100
How to write query in oracle? first sum(distinct price) group by id then sum(all price).
oracle中如何写查询？第一个总和（不同价格）按 id 分组，然后总和（所有价格）。
回答by Gordon Linoff
I would be very careful with a data structure like this. First, check that all ids have exactly one price:
我会非常小心这样的数据结构。首先，检查所有ids 是否只有一个价格：
select id
from table t
group by id
having count(distinct price) > 1;
I think the safest method is to extract a particular price for each id(say the maximum) and then do the aggregation:
我认为最安全的方法是为每个提取特定的价格id（比如最大值），然后进行聚合：
select sum(price)
from (select id, max(price) as price
from table t
group by id
) t;
Then, go fix your data so you don't have a repeated additive dimension. There should be a table with one row per id and price (or perhaps with duplicates but controlled by effective and end dates).
然后，去修复你的数据，这样你就没有重复的附加维度。应该有一个表，每个 ID 和价格一行（或者可能有重复但受有效和结束日期控制）。
The data is messed up; you should not assume that the price is the same on all rows for a given id. You need to check that every time you use the fields, until you fix the data.
数据乱七八糟；您不应该假设给定 ID 的所有行的价格都相同。每次使用字段时都需要检查，直到修复数据。
回答by Lalit Kumar B
first sum(distinct price) group by id then sum(all price)
第一个总和（不同价格）按 id 分组，然后总和（所有价格）
Looking at your desired output, it seems you also need the final sum(similar to ROLLUP), however, ROLLUPwon't directly work in your case.
查看您想要的输出，似乎您还需要最终总和（类似于 ROLLUP），但是，ROLLUP不会直接适用于您的情况。
If you want to formatyour output in exactly the way you have posted your desired output, i.e. with a headerfor the last row of total sum, then you could set the PAGESIZEin SQL*Plus.
如果您想完全按照您发布所需输出的方式来格式化输出，即总和的最后一行带有标题，那么您可以在SQL*Plus 中设置PAGESIZE。
- Using UNION ALL
- 使用UNION ALL
For example,
例如，
SQL> set pagesize 7
SQL> WITH DATA AS(
2 SELECT ID, SUM(DISTINCT price) AS price
3 FROM t
4 GROUP BY id
5 )
6 SELECT to_char(ID) id, price FROM DATA
7 UNION ALL
8 SELECT 'ID' id, sum(price) FROM DATA
9 ORDER BY ID
10 /
ID PRICE
--- ----------
1 30
2 20
3 30
4 20
ID PRICE
--- ----------
ID 100
SQL>
So, you have an additional row in the end with the total SUMof price.
因此，您最后有一个额外的行，其中包含价格的总和。
- Using ROLLUP
- 使用汇总
Alternatively, you could use ROLLUPto get the total sum as follows:
或者，您可以使用ROLLUP获得总和，如下所示：
SQL> set pagesize 7
SQL> WITH DATA AS
2 ( SELECT ID, SUM(DISTINCT price) AS price FROM t GROUP BY id
3 )
4 SELECT ID, SUM(price) price
5 FROM DATA
6 GROUP BY ROLLUP(id);
ID PRICE
---------- ----------
1 30
2 20
3 30
4 20
ID PRICE
---------- ----------
100
SQL>
回答by dnoeth
First do the DISTINCT and then a ROLLUP
先做 DISTINCT 然后做 ROLLUP
SELECT ID, SUM(price) -- sum of the distinct prices
FROM
(
SELECT DISTINCT ID, price -- distinct prices per ID
FROM tab
) dt
GROUP BY ROLLUP(ID) -- two levels of aggregation, per ID and total sum
回答by MT0
Oracle 11g R2 Schema Setup:
Oracle 11g R2 架构设置：
CREATE TABLE MYTABLE ( ID, price ) AS
SELECT 1, 10 FROM DUAL
UNION ALL SELECT 1, 10 FROM DUAL
UNION ALL SELECT 1, 20 FROM DUAL
UNION ALL SELECT 2, 20 FROM DUAL
UNION ALL SELECT 2, 20 FROM DUAL
UNION ALL SELECT 3, 30 FROM DUAL
UNION ALL SELECT 3, 30 FROM DUAL
UNION ALL SELECT 4, 5 FROM DUAL
UNION ALL SELECT 4, 5 FROM DUAL
UNION ALL SELECT 4, 15 FROM DUAL;
Query 1:
查询 1：
SELECT COALESCE( TO_CHAR(ID), 'ID' ) AS ID,
SUM( PRICE ) AS PRICE
FROM ( SELECT DISTINCT ID, PRICE FROM MYTABLE )
GROUP BY ROLLUP ( ID )
ORDER BY ID
结果：
| ID | PRICE |
|----|-------|
| 1 | 30 |
| 2 | 20 |
| 3 | 30 |
| 4 | 20 |
| ID | 100 |
回答by Raging Bull
SELECT ID,SUM(price) as price
FROM
(SELECT ID,price
FROM TableName
GROUP BY ID,price) as T
GROUP BY ID
Explanation:
解释：
The inner query will select different prices for each ids.
内部查询将为每个 ID 选择不同的价格。
i.e.,
IE，
ID price
-------------
1 10
1 20
2 20
3 30
4 5
4 15
Then the outer query will select SUMof those prices for each id.
然后外部查询将为SUM每个 id选择这些价格。
Final Result :
最后结果 ：
ID price
----------
1 30
2 20
3 30
4 20
Result in SQL Fiddle.
结果在SQL Fiddle。
