CODEWITHSUNDEEP
sqldatabaseoracle-databasegroup-by
Alexandr
Alexandr

Reputation: 9505

Counting by grouping query in Oracle using analytical functions

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)

Upvotes: 0

Views: 1100

Answers (2)

Florin Ghita
Florin Ghita

Reputation: 17643

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.

Upvotes: 1

Steven Schroeder
Steven Schroeder

Reputation: 6194

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;

Upvotes: 3

Related Questions