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

Question

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;

Answer 1

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