CODEWITHSUNDEEP
sqlpostgresql
DBE7
DBE7

Reputation: 816

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!

Upvotes: 0

Views: 184

Answers (1)

Parfait
Parfait

Reputation: 107767

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

Upvotes: 3

Related Questions