David Bentzon-Ehlers David Bentzon-Ehlers - 6 months ago 14
SQL Question

Problems with my WHERE clause (SQL)

I'm trying to write a query that returns the following columns:

owner_id,
number_of_concluded_bookings,
number_of_declined_bookings,
number_of_inquiries


However, the problem is that my WHERE clause messes up the query because I am querying the same table. Here is the code:

SELECT owner_id,
Count(*) AS number_of_cancelled_bookings
FROM bookings
WHERE state IN ('cancelled')
GROUP BY owner_id
ORDER BY 1;


It's easy to retrieve the columns individually, but I want all of them. Say I wanted
number_of_concluded_bookings
as well, that would mean I'd have to alter the WHERE clause ...

Help is greatly appreciated!

Answer

Consider conditional aggregations:

SELECT owner_id,
       SUM(CASE WHEN state='concluded' THEN 1 ELSE 0 END) AS number_of_concluded_bookings,
       SUM(CASE WHEN state='cancelled' THEN 1 ELSE 0 END) AS number_of_cancelled_bookings,
       SUM(CASE WHEN state='declined' THEN 1 ELSE 0 END) AS number_of_declined_bookings,
       SUM(CASE WHEN state='inquiries' THEN 1 ELSE 0 END) AS number_of_inquiries
FROM bookings
GROUP BY owner_id
Comments