Home > Ask the Oracle Database / Applications Experts > SQL Questions & Answers > Use HAVING with CASE to count specific instances
Ask The Oracle Expert: Questions & Answers
EMAIL THIS

Use HAVING with CASE to count specific instances

Rudy Limeback EXPERT RESPONSE FROM: Rudy Limeback

Pose a Question
Other Oracle Categories
Meet all Oracle Experts
Become an Expert for this site


Oracle tips, scripts, and expert advice
Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us    Add to Google


>
QUESTION POSED ON: 20 July 2007

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)?



Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us    Add to Google


RELATED CONTENT
SQL
How to check SQL query construction with the Mimer Validator
Using the SQL GROUP BY clause for counting combinations
How to use an SQL CASE expression
How to sort an SQL UNION query with special ORDER BY sequence
How to use string functions to make an SQL join
An SQL solution for a customer order homework problem
How to use SQL's POSITION function with substrings
Using SQL date functions to get totals for last three days
Using CASE in the SQL ORDER BY clause
What's the difference between an SQL inner join and equijoin?

Oracle and SQL
Using the SQL GROUP BY clause for counting combinations
How to use an SQL CASE expression
How to use the Oracle Database SQL Reference Manual
How to use SQL Developer to run SQL statements
How to work with the Oracle database home page
How to use SQL*Plus in Oracle
How to use SQL Developer to work with an Oracle database
How to view and edit table column definitions
How to sort an SQL UNION query with special ORDER BY sequence
How to use string functions to make an SQL join
Oracle and SQL Research

RELATED GLOSSARY TERMS
Terms from Whatis.com − the technology online dictionary
autonomous transaction  (SearchOracle.com)
CFML  (SearchOracle.com)
dynamic SQL  (SearchOracle.com)
foreign key  (SearchOracle.com)
Java Database Connectivity  (SearchOracle.com)
Open Database Connectivity  (SearchOracle.com)
Oracle  (SearchOracle.com)
stored procedure  (SearchOracle.com)
The Open Group  (SearchOracle.com)

RELATED RESOURCES
2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems
Search Bitpipe.com for the latest white papers and business webcasts
Whatis.com, the online computer dictionary


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.



Search and Browse the Expert Answer Center
Search and browse more than 25,000 question and answer pairs from more than 250 TechTarget industry experts.
Browse our Expert Advice



Oracle White Papers: Fusion Middleware
HomeNewsTopicsTipsAsk the ExpertsMultimediaWhite PapersProductsBlogs
About Us  |  Contact Us  |  For Advertisers  |  For Business Partners  |  Site Index  |  RSS
SEARCH 
TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of technology-specific websites, events and online magazines.

TechTarget Corporate Web Site  |  Media Kits  |  Site Map




All Rights Reserved, Copyright 2003 - 2009, TechTarget | Read our Privacy Policy 		  TechTarget - The IT Media ROI Experts