Search code examples
sqloracle-databaseselectaggregate-functions

SQL How to do a conditional sum by distinct id?

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

DATE            cost

01/01/2011         20

I first tried something like 

    sum(distinct cost)

but that of curse only returns 10 I also tried:

sum(case when distinct id then cost else 0 end)

but that is not a functional query.

Solution

  • 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:

    select date, sum(cost)
from 
  (select distinct date, id, cost from YourTable)
group by date

    Updated

    Oracle derived tables do not require alias.