David Bentzon-Ehlers David Bentzon-Ehlers - 1 year ago 72
SQL Question

Problems with my WHERE clause (SQL)

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


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

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

Help is greatly appreciated!

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download