Counting by grouping query in Oracle using analytical functions

Question

I've got a table:

select personID, categoryCode from entries;

6300936427  083
6314642671  084
6314742066  081
6346724201  083
...

I can count the number of persons in each group by:

select categoryCode, count(e.ID)
from entries e
group by e.categoryCode
order by e.categoryCode;

060 56
062 15
081 377
082 374

I want to do the same calculation, but for some choosen categories I want to count them together. For instance I want if categoryCode IN ('081', '082') I wish to count results for them. And my result set should be calculated as:

select ...

060 56
062 15
080 751 --(377 + 374) counts, that correspond to 081 and 082 categoryGroup

How can I archive this using Oracle analytical functions? (Oracle version 10)

Steven Schroeder · Accepted Answer · 2012-02-10 06:28:05Z

3

I think that you can achieve what you want simply by extending your existing query. It looks to me like you are mapping the categoryCode '081' and '082' to a combinedCategoryCode of '080', and that is the sum of the two other counts. So, you can use a CASE statement to do the mapping as follows. You will have to map all the categoryCodes you want to individual combinedCategoryCodes. The ELSE clause takes care of any categoryCodes you are not mapping

SELECT CASE t.categoryCode
           WHEN '083' THEN '080'
           WHEN '084' THEN '080'
           ELSE t.categoryCode
       END AS combinedCategoryCode,
       SUM(t.[count]) AS combinedCount
FROM
    (
    -- This is your original query
    select categoryCode, count(e.ID) as [count]
    from entries e
    group by e.categoryCode
    ) t
GROUP BY CASE t.categoryCode
             WHEN '083' THEN '080'
             WHEN '084' THEN '080'
             ELSE t.categoryCode
         END
ORDER BY 1;

answered Feb 10, 2012 at 6:28

Steven Schroeder

6,2742 gold badges23 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alexandr Over a year ago

thank you, your solution works, but is there any way to do the same calculation using Oracle analytical functions?

Florin Ghita · Accepted Answer · 2012-02-10 08:38:41Z

Steven's query can be rewritten:

SELECT 
       CASE  
           WHEN t.categoryCode in ('083', '084') THEN '080' 
           ELSE t.categoryCode 
       END AS combinedCategoryCode, 
       count(e.id) AS combinedCount 
FROM 
   entries t 
GROUP BY  
       CASE  
           WHEN t.categoryCode in ('083', '084') THEN '080' 
           ELSE t.categoryCode 
       END 
ORDER BY 1;

But: You can create a configuration table, config_categ

categoryCode  categ_result
060           060 
062           062
081           080
082           080

and the query will be:

SELECT 
   c.categ_result, 
   count(e.id) AS combinedCount 
FROM 
   entries t 
   join config_categ c on (t.categoryCode = c.categoryCode) 
GROUP BY  
   c.categ_result    
ORDER BY 1;

PS: You don't need a analytic function for this. It is a simple group by.

Collectives™ on Stack Overflow

Counting by grouping query in Oracle using analytical functions

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related