I would like to count the waves available (LEFT OUT JOIN). My problem is that I also have another LEFT OUTER JOIN on invitations.

The idea is to have all the stats from the same SQL request. Stats from invitations and stats from waves.

An event has many waves An event has many invitations

CREATE TABLE public.events (
    id bigint NOT NULL,
    uuid uuid DEFAULT public.gen_random_uuid() NOT NULL
    name character varying NOT NULL
);
CREATE TABLE public.invitations (
    id bigint NOT NULL,
    uuid uuid DEFAULT public.gen_random_uuid() NOT NULL,
    event_id bigint NOT NULL
);
CREATE TABLE public.invitation_waves (
    id bigint NOT NULL,
    uuid uuid DEFAULT public.gen_random_uuid() NOT NULL,
    name character varying NOT NULL,
    wavable_type character varying,
    wavable_id bigint,
    scheduled_at timestamp(6) without time zone
);

I have tested 2 options

Option 1

SELECT CAST(sum((case when (waves.id IS NOT NULL) then 1 else 0 end)) AS INTEGER) as total_waves
FROM "events"
         LEFT OUTER JOIN "waves" ON "waves"."wavable_type" = 'Event' AND
                                               "waves"."wavable_id" = "events"."id"
         LEFT OUTER JOIN "invitations" ON "invitations"."event_id" = "events"."id"
WHERE "events"."uuid" = 'XXX'
GROUP BY "events"."id"

-> It gives me the number of invitations created if above the number of waves

Option 2 (with DISTINCT)

SELECT CAST(sum(DISTINCT(case when (waves.id IS NOT NULL) then 1 else 0 end)) AS INTEGER) as total_waves
FROM "events"
         LEFT OUTER JOIN "waves" ON "waves"."wavable_type" = 'Event' AND
                                               "waves"."wavable_id" = "events"."id"
         LEFT OUTER JOIN "invitations" ON "invitations"."event_id" = "events"."id"
WHERE "events"."uuid" = 'XXX'
GROUP BY "events"."id"

-> Always returns me 1 which is, I suppose, the number of events matching the request (I've scoped it with the uuid).

Not having the DISTINCT is actually multiplying the correct sum by the number of waves.

Having the DISTINCT restrict the entire sum to the number of waves.

Option 3 (sub select) ✅

I'm currently trying this option based on this answer. It is working but I'm concerned about the dependency between GROUP BY and the selected columns from the LEFT OUTER JOIN.

SELECT CAST(sum((case
                     when (invitations.status = 4 or invitations.status = 5 or invitations.status = 6) then 1
                     else 0 end)) AS INTEGER) as total_validated,
       total_waves,
       waves_scheduled
FROM "events"
         LEFT OUTER JOIN "invitations" ON "invitations"."event_id" = "events"."id"
         LEFT OUTER JOIN (SELECT distinct on (wavable_id)
                                 wavable_id                                                                                      as event_id,
                                 CAST(sum((case when (invitation_waves.id IS NOT NULL) then 1 else 0 end)) AS INTEGER)           as total_waves,
                                 CAST(sum((case when (invitation_waves.scheduled_at IS NOT NULL) then 1 else 0 end)) AS INTEGER) as waves_scheduled
                          FROM invitation_waves
                          WHERE wavable_type = 'Event'
                          GROUP BY 1 
                          ORDER BY wavable_id) as "wave_stats" on "wave_stats"."event_id" = "events"."id"
WHERE "events"."uuid" = '1ee5ec72-6f3c-404c-871c-5c5724f6a1ed'
GROUP BY "events"."id", total_waves, waves_scheduled

Final version

Thanks to @Thorsten, I pick an hybrid version between WITH and direct LEFT JOIN.

Invitation table was too big (and also joined to another to use a WITH and FILTER I suppose and I needed to GROUP BY every generated column from the with.

So here is my final version

WITH "wave_stats" AS (SELECT wavable_id                                                 as event_id,
                             COUNT(DISTINCT id)                                         AS total_waves,
                             COUNT(DISTINCT id) FILTER (where scheduled_at IS NOT NULL) AS waves_scheduled
                      FROM "waves"
                      WHERE "waves"."wavable_type" = 'Event'
                      GROUP BY "waves"."wavable_id")
SELECT events.*,
       CAST(COUNT(invitations.id) AS INTEGER) AS total_invitations, CAST (sum((case
    when (invitations.status = 4 or invitations.status = 5 or invitations.status = 6) then 1
    else 0 end)) AS INTEGER) as total_validated, case when wave_stats.waves_scheduled is null then 0 else wave_stats.waves_scheduled
end,
       case when wave_stats.total_waves is null then 0 else wave_stats.total_waves
end FROM "events"
         LEFT OUTER JOIN "invitations" ON "invitations"."event_id" = "events"."id"
         LEFT JOIN wave_stats ON wave_stats.event_id = events.id
GROUP BY "events"."id", "total_waves", "waves_scheduled"

You want to count invitations and waves per event. For this to happen you join the invitations and waves to an event, but this gives you a cartesian product, i.e. for an event with, say, three invitations and four waves you'll produce 3 x 4 = 12 rows. Then you try to muddle through somehow to get the counts you want.

Instead of joining the invitations and waves to an event, join the invitation count and the wave count to an event. In other words: aggregate before joining.

WITH
  event_invitations AS
  (
    SELECT
      event_id,
      COUNT(*) AS total
    FROM invitations
    GROUP BY event_id
  ),
  event_waves AS
  (
    SELECT
      wavable_id AS event_id,
      COUNT(*) AS total,
      COUNT(scheduled_at) AS scheduled
    FROM waves
    WHERE wavable_type = 'Event'
    GROUP BY wavable_id
  )
SELECT
  e.*,
  COALESCE(i.total, 0) AS total_invitation_count,
  COALESCE(w.total, 0) AS total_wave_count,
  COALESCE(w.scheduled, 0) AS scheduled_wave_count
FROM events e
LEFT OUTER JOIN event_invitations i ON i.event_id = e.id
LEFT OUTER JOIN event_waves w ON w.event_id = e.id
ORDER BY e.id;