How to use distinct and sum both together in oracle?

Question

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).

Gordon Linoff · Answer 1 · 2015-06-16T10:44:25.113

I would be very careful with a data structure like this. First, check that all ids have exactly one price:

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:

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).

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.

Lalit Kumar B · Answer 2 · 2015-06-16T10:34:21.600

first sum(distinct price) group by id then sum(all price)

Looking at your desired output, it seems you also need the final sum(similar to ROLLUP), however, ROLLUP won't directly work in your case.

If you want to format your output in exactly the way you have posted your desired output, i.e. with a header for the last row of total sum, then you could set the PAGESIZE in SQL*Plus.

Using 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 SUM of price.

Using ROLLUP

Alternatively, you could use ROLLUP to get the total sum as follows:

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>

Raging Bull · Answer 3 · 2015-06-16T12:11:15.547

0

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.

i.e.,

ID    price    
-------------     
 1     10 
 1     20     
 2     20      
 3     30
 4     5
 4     15

Then the outer query will select SUM of those prices for each id.

Final Result :

ID  price
----------
1   30
2   20
3   30
4   20

Result in SQL Fiddle.

edited Jun 16 '15 at 12:11

answered Jun 16 '15 at 10:07

Raging Bull

18,593
13
50
55

Why would you say "first select the distinct prices" and then use group by to do that? Using distinct in the subquery would make things a lot clearer! – Boneist Jun 16 '15 at 11:33
@Boneist: I know it would. I am giving an explanation for what my query does. – Raging Bull Jun 16 '15 at 11:35
I would suggest you amend your answer. I don't understand why, given the choice of a clear, self-documenting way of doing something, you would pick the one that is less clear and open to misinterpretation! – Boneist Jun 16 '15 at 12:08
@Boneist: Updated my answer with explanation. – Raging Bull Jun 16 '15 at 12:13
I think you've missed my point. Why are you using group by to do the distinct, when you're not doing any aggregation? Why not just use distinct? eg. `select distinct id, price from tablename` – Boneist Jun 16 '15 at 12:18

score 0 · Answer 4 · answered Jun 16 '15 at 10:43

First do the DISTINCT and then a 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

score 0 · Answer 5 · answered Jun 16 '15 at 13:20

SQL Fiddle

Oracle 11g R2 Schema Setup:

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:

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

Results:

| ID | PRICE |
|----|-------|
|  1 |    30 |
|  2 |    20 |
|  3 |    30 |
|  4 |    20 |
| ID |   100 |

How to use distinct and sum both together in oracle?

5 Answers5

Linked