SQL How to do a conditional sum by distinct id?

Question

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.

Answer 1

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.