Q:

Aggregate on multiple conditions: convert to a single query

Given the tables:
reservations (id, place_id, confirmed_at, paid_at)
and
places (id, name)
I need to return the aggregate that can be expressed the following queries separately:
-- Confirmed
SELECT places.id, places.name, COUNT(reservations.*) as total_confirmed
FROM reservations
  INNER JOIN places ON places.id = reservations.place_id
WHERE
  reservations.confirmed_at IS NOT NULL
GROUP BY places.id, places.name

--  Paid
SELECT places.id, places.name, COUNT(reservations.*) as total_paid
FROM reservations
  INNER JOIN places ON places.id = reservations.place_id
WHERE
  reservations.paid_at IS NOT NULL
GROUP BY places.id, places.name

-- Paid Uncofirmed
SELECT places.id, places.name, COUNT(reservations.*) as total_paid_unconfirmed
FROM reservations
  INNER JOIN places ON places.id = reservations.place_id
WHERE
  reservations.paid_at IS NOT NULL AND reservations.confirmed_at IS NULL
GROUP BY places.id, places.name

How do I rewrite these queries into a single one and return all the necessary?

A:

SELECT places.id, places.name,
sum(case when (reservations.confirmed_at IS NOT NULL) then 1 else 0 end) as total_confirmed,
sum(case when (reservations.paid_at  IS NOT NULL) then 1 else 0 end) as total_paid,
sum(case when  (reservations.paid_at IS NOT NULL AND reservations.confirmed_at IS NULL) then 1 else 0 end) as total_confirmed_paid
FROM reservations
  INNER JOIN places ON places.id = reservations.place_id
GROUP BY places.id, places.name

A:

I prefer to have a query like this, because what if there are some places that have no reservation yet or may have multiple reservations already. Still it's safe to calculate the SUM.
SELECT  d.*, a.total_confirmed, b.total_paid, c.total_paid_unconfirmed
FROM    places d
        LEFT JOIN
        (
            SELECT places.id, places.name, COUNT(reservations.*) as total_confirmed
            FROM reservations
              INNER JOIN places ON places.id = reservations.place_id
            WHERE
              reservations.confirmed_at IS NOT NULL
            GROUP BY places.id, places.name
        ) a ON d.id = a.id
        LEFT JOIN
        (
            SELECT places.id, places.name, COUNT(reservations.*) as total_paid
            FROM reservations
              INNER JOIN places ON places.id = reservations.place_id
            WHERE
              reservations.paid_at IS NOT NULL
            GROUP BY places.id, places.name
        ) b ON d.id = b.id
        LEFT JOIN
        (
            SELECT places.id, places.name, COUNT(reservations.*) as total_paid_unconfirmed
            FROM reservations
              INNER JOIN places ON places.id = reservations.place_id
            WHERE
              reservations.paid_at IS NOT NULL AND reservations.confirmed_at IS NULL
            GROUP BY places.id, places.name
        ) c ON d.id = c.id

