Oscar Gomez Oscar Gomez - 1 year ago 89
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 Source

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)
  (select distinct date, id, cost from YourTable)
group by date


Oracle derived tables do not require alias.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download