Use HAVING with CASE to count specific instances
I'm trying to construct a SELECT statement that will return people that have NO live courses running at the moment.
I'm trying to construct a SELECT statement that will return people that have NO live courses running at the moment. Please see example table below:
id Name Course StartDate Status 101 JohnS Course1 01/04/2007 Completed 101 JohnS Course2 01/04/2007 Completed 101 JohnS Course3 01/04/2007 Live 101 JohnS Course4 01/04/2007 Completed 101 JohnS Course5 01/04/2007 Completed
I need to find people who have live courses, which is simple enough (WHERE Status = 'Live'). But I also need another query to find people who have no live courses at all. If I do the obvious (WHERE status <> 'Live') it will record John Smith not being live as he has four completed courses where the status is NOT live.
From the above example, how can I get John Smith NOT appearing on a select statement when I'm looking for people who are not live at all (as he has one course that is live)?
There are several ways to approach this—self-joins, NOT EXISTS subqueries, and so on. But perhaps the simplest approach involves a GROUP BY:
select Name from Courses group by Name having sum( case when Status = 'Live' then 1 else 0 end ) = 0
The GROUP BY ensures that we get one result row for everybody in the table. (This is harder to achieve with other approaches.)
The solution involves simply counting the number of Live courses, and keeping only those Names where this count is 0.
Dig Deeper on Oracle development languages
Related Q&A from Rudy Limeback
Using the SQL GROUP BY clause for counting combinations
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
How to sort an SQL UNION query with special ORDER BY sequence
SQL expert Rudy Limeback explains how to sort an SQL UNION query using a special ORDERY BY sequence. Continue Reading
Using an SQL SELECT statement from a non-existing table
SQL expert Rudy Limeback explains how to formulate a query using an SQL SELECT statement from a non-existing table. Continue Reading