Q

A CASE expression inside a COUNT function

Could you please check my CASE query? I just want to include all statuses that were performed by a person across the row, but it seems the query repeats for every row with each status.

Could you please check my CASE query? I just want to include all statuses that were performed by a person across the row, but it seems the query repeats for every row with each status.

OWNER_ID OWNER INPROCESS PENDING CLOSED
 10004   Jose      3        0      0
 10004   Jose      0        2      0
 10004   Jose      0        0      2
 10005   Karen     0        7      0
 10005   Karen     0        0      4

Here is my query:

select attk.owner_id,
aron.owner_first_name || ' ' || aron.owner_last_name "OWNER",
case when upper(attk.status) like 'IN PROCESS%' 
then count(attk.status) else 0 end "IN PROCESS",
case when upper(attk.status) like 'PENDING%'
then count(attk.status) else 0 end "PENDING",
case when upper(attk.status) like 'CLOSE%' 
then count(attk.status) else 0 end "CLOSE"
from atst_ticket attk, atsr_owner_names aron
where attk.issue_raised_date 
between '01-OCT-05' and '31-OCT-05'
and attk.owner_id = aron.owner_id
group by attk.owner_id, 
aron.owner_first_name || ' ' || aron.owner_last_name,
attk.status

Hope to hear from you as soon as possible.

Congratulations, you were really close with your query. You are definitely on the right track by using CASE expressions.

What's happening is that your query is returning one row per status per person. This is easy to see from the GROUP BY clause, which includes the status column.

To solve this problem, you could remove the status column from the GROUP BY. But you probably already tried this, and got a database syntax error, something about invalid grouping. That's because the status column is also in the SELECT list as a non-aggregate column. And since SQL requires that every non-aggregate column in the SELECT list must also be in the GROUP BY, you get an error if you remove status from the GROUP BY.

The way around this predicament is to do away with the non-aggregate column in the SELECT list. In your query, the CASE expression has a non-aggregate expression, UPPER(attk.status), as well as an aggregate expression, COUNT(attk.status). Note that the non-aggregate expression is outside of the aggregate expression. Let's turn this inside out, and put the CASE expression inside the aggregate expression. At the same time, we'll change the COUNT to SUM, since we're going to add up a series of 1s and 0s:

select attk.owner_id
     , aron.owner_first_name 
       || ' ' || aron.owner_last_name  "OWNER"
     , sum(case when upper(attk.status) 
                     like 'IN PROCESS%' 
                then 1 else 0 end)   "IN PROCESS"
     , sum(case when upper(attk.status) 
                     like 'PENDING%' 
                then 1 else 0 end)   "PENDING"
     , sum(case when upper(attk.status) 
                     like 'CLOSE%' 
                then 1 else 0 end)   "CLOSE"
  from atst_ticket attk
inner
  join atsr_owner_names aron
    on attk.owner_id = aron.owner_id
 where attk.issue_raised_date 
       between '01-OCT-05' and '31-OCT-05'
group 
    by attk.owner_id
       , aron.owner_first_name 
         || ' ' || aron.owner_last_name

With the CASE inside the SUM function, it is now part of an aggregate expression, and therefore the status column can be removed from the GROUP BY.

This was first published in November 2005

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

1 comment

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close