SQL: sum of a value but for distinct ids only - conditional sum?

conditional-statements, sql, sum

Solution

My general approach to this is to pre-aggregate each table, before joining.

Partly because you're not actually summing distinct values (if each of the two rows had `10`, the answer is still `20`).

But mostly because it's actually simpler that way. The sub-queries do the aggregation, then the joins are all 1:1.

SELECT
  days.id,
  typ_agg.rows,
  type2_agg.rows,
  type2_agg.duration
FROM
  days
LEFT JOIN
  (SELECT fk_day, COUNT(*) as rows FROM typ GROUP BY fk_day)  AS typ_agg
    ON days.id = typ_agg.fk_day
LEFT JOIN
  (SELECT fk_day, COUNT(*) as rows, SUM(duration) as duration FROM typ2 GROUP BY fk_day)  AS typ2_agg
    ON days.id = typ2_agg.fk_day

Problem

I have following structure: Day with multiple events of typ1 and typ2, where typ1 and typ2 have foreign keys to their respective days. Typ2 also has duration. Now I want to count all typ1 events, all typ2 events and sum of the typ2 duration. Example Data: Day: ``` ID = 1 | Date = yesterday | ... ``` Typ1: ``` ID = 1 | FK_DAY = 1 | ... ID = 2 | FK_DAY = 1 | ... ``` Typ2: ``` ID = 1 | FK_DAY = 1 | duration = 10 ID = 2 | FK_DAY = 1 | duration = 20 ``` I now want the result: ``` Day.ID = 1 | countTyp1 = 2 | countTyp2 = 2 | sumDurationTyp2 = 30 ``` My problem is the sum, I need something like "sum for distinct typ2.ID"... Does anyone know a way to solve that? I'm using something like the following, but that of course does not work the way I want: ``` SELECT day.id, count( DISTINCT typ1.id ), count( DISTINCT typ2.id ), sum( duration ) AS duration FROM days LEFT JOIN typ ON day.id = typ1.id LEFT JOIN typ2 ON day.id = typ2.id GROUP BY day.id; ```

Original source