Ask the Expert

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.

 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,

Hope to hear from you as soon as possible.

    Requires Free Membership to View

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: