Oscar Gomez Oscar Gomez - 1 month ago 12
SQL Question

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.

Answer

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.