Problem solve Get help with specific problems with your technologies, process and projects.

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
    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.

This was last published in July 2007

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.