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
-
![]()
Set up a Terraform S3 backend with this video tutorial
By: Christopher Blackden
-
![]()
Value-Based Payments in Medicaid Tied to More Behavioral Health Visits
By: Victoria Bailey
-
![]()
Install Calico for Kubernetes in this video walkthrough
By: Christopher Blackden
-
![]()
Medicaid Coverage Linked to Rise in Telehealth Use, Healthcare Access
By: Anuja Vaidya
