CODEWITHSUNDEEP
sqloracle-databasegroup-bysum
BadLeo
BadLeo

Reputation: 435

Sum of distinct items values Oracle SQL

I got data like this:

ITEM    COLOR   VOL
1       RED     3
2       BLUE    3
3       RED     3
4       GREEN   12
5       BLUE    3
6       GREEN   12

and I want to have the total sum of each color, mean RED + BLUE + GREEN = 3+3+12 = 18

P.S I can't do it in sub-query since it is a part of a big query already. I am looking for a way could do it in select clause.something like 

select sum(distinct(COLOR) VOL) from myTable group by COLOR

Thanks a lot!

Upvotes: 1

Views: 3122

Answers (3)

user5683823
user5683823

Reputation: 

sum(max(vol)) from ... group by color

will work, but it's not clear why you should need such a thing. Likely this sum can be computed (much) earlier in your query, not right at the end.

Proof of concept (on a standard Oracle schema):

SQL> select sum(max(sal)) from scott.emp group by deptno;

SUM(MAX(SAL))
-------------
        10850

1 row selected.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522346

One approach uses a CTE or subquery to find the mean volumes for each color. Then take the sum of all mean volumes, for all colors.

WITH cte AS (
    SELECT COLOR, AVG(VOL) AS VOL   -- or MIN(VOL), or MAX(VOL)
    FROM yourTable
    GROUP BY COLOR
)
SELECT SUM(t.VOL)
FROM cte t

Upvotes: 3

JohnHC
JohnHC

Reputation: 11205

Sum the sum of distinct, as grouped by color

select sum(sum(distinct VOL))
from MyTable
group by COLOR

Tested locally and here

Upvotes: 3

Related Questions