CODEWITHSUNDEEP
sqlruby-on-railspostgresqlaggregate-functions
Dmytrii Nagirniak
Dmytrii Nagirniak

Reputation: 24108

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?

Upvotes: 1

Views: 1647

Answers (3)

Vikram Jain
Vikram Jain

Reputation: 5588

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

Upvotes: 4

John Woo
John Woo

Reputation: 263843

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

Upvotes: 1

Seasoned
Seasoned

Reputation: 1059

these queries can be combined together using union, but in union, all the queries must return same result set, for that the output of all queries must be made same. This can be done as follows:

SELECT places.id, places.name,'total_confirmed' as totalType, COUNT(reservations.*) as total
FROM reservations   INNER JOIN places ON places.id = reservations.place_id 
WHERE   reservations.confirmed_at IS NOT NULL 
GROUP BY places.id, places.name  
union all
SELECT places.id, places.name,'total_paid' as totalType, COUNT(reservations.*) as total
FROM reservations   INNER JOIN places ON places.id = reservations.place_id 
WHERE   reservations.paid_at IS NOT NULL 
GROUP BY places.id, places.name  
union all
SELECT places.id, places.name,'total_paid_unconfirmed' as totalType, COUNT(reservations.*) as total
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

Upvotes: 1

Related Questions