SQL oracle : Group and Count data

Question

I have a table like this:

+-----+------+
| acc | CASE |
+-----+------+
| 001 | a    |
| 001 | b    |
| 001 | c    |
| 002 | a    |
| 002 | b    |
| 003 | b    |
| 003 | c    |
| 004 | a    |
| 005 | b    |
| 006 | b    |
| 007 | a    |
| 007 | b    |
| 007 | c    |
| 008 | a    |
| 008 | b    |
| n   | x    |
+-----+------+

I have no idea how to group and count data with

+-----------+-----------+
|   case    | count_acc |
+-----------+-----------+
| a         | 1         |
| b         | 2         |
| c         | 0         |
| a+b       | 2         |
| b+c       | 1         |
| a+b+c     | 2         |
| a+b+c+…+x | n         |
+-----------+-----------+

in case a+b,b+c ... a+b+c+…+x I can't group case and count acc. Do you have any idea to group and count?

Can you explain the logic behind your expected output? It is not clear to me. — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 20, 2018 at 9:14
This is very hard in SQL as the permutations are not know in advance. — dnoeth
– dnoeth, Commented Mar 20, 2018 at 9:16

Watcharin Chimmachuy · Accepted Answer · 2018-03-20 11:29:36Z

1

select b.case,count(distinct(a.acc)) as account from
test  a , (select acc  , rtrim(case,'+') case
from ( select acc , case , rn from test
           model
           partition by (acc)
           dimension by (row_number() over (partition by acc order by case) rn )
           measures  (cast(case as varchar2(10)) case)
           rules
           (case[any] order by rn desc = case[cv()]||'+'||case[cv()+1])
     )
where rn = 1) b
where a.acc = b.acc
group by b.case

edited Mar 20, 2018 at 11:29

answered Mar 20, 2018 at 10:02

Watcharin Chimmachuy

395 bronze badges

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

Comments

psaraj12 · Accepted Answer · 2018-03-20 10:37:34Z

You can achieve the same using LISTAGG in oracle

with test as ( select  001 acc , 'a' case   FROM DUAL UNION
SELECT  001 , 'b'     FROM DUAL UNION
SELECT  001 , 'c'     FROM DUAL UNION
SELECT  002 , 'a'     FROM DUAL UNION
SELECT  002 , 'b'     FROM DUAL UNION
SELECT  003 , 'b'     FROM DUAL UNION
SELECT  003 , 'c'    FROM DUAL UNION
SELECT  004 , 'a'     FROM DUAL UNION
SELECT  005 , 'b'     FROM DUAL UNION
SELECT  006 , 'b'     FROM DUAL UNION
SELECT  007 , 'a'     FROM DUAL UNION
SELECT  007 , 'b'    FROM DUAL UNION
SELECT  007 , 'c'     FROM DUAL UNION
SELECT  008 , 'a'     FROM DUAL UNION
SELECT  008 , 'b'     FROM DUAL )
select case,count(1) from
(
SELECT count(1),acc, LISTAGG(case, '+') WITHIN GROUP (ORDER BY acc) AS case
FROM   test
GROUP BY acc) GROUP BY case order by 1;

SQL fiddle here

Fábio Galera · Accepted Answer · 2018-03-20 11:04:24Z

0

First you have to organize your data, grouping the ACC and Aggregating the CASE

SELECT 
    LISTAGG (case,'+') WITHIN GROUP (ORDER BY case) case, 
    ACC 
FROM TEST 
GROUP BY ACC

Them you will be able to count:

SELECT case, count(*) FROM (
    SELECT 
        LISTAGG (case,'+') WITHIN GROUP (ORDER BY case) case, 
        ACC 
    FROM TEST 
    GROUP BY ACC
) GROUP BY case
ORDER BY CASE;

In case you don't have function LISTAGG, below 11g for example, refer to this website:

http://oracle-base.com/articles/misc/string-aggregation-techniques

edited Mar 20, 2018 at 11:04

answered Mar 20, 2018 at 10:10

Fábio Galera

12511 bronze badges

3 Comments

Watcharin Chimmachuy Over a year ago

I can't use LISTAGG because my oracle is 10.2.0.5.0.

psaraj12 Over a year ago

Please don't copy existing answer and post as new answer as it solves no purpose.If you need to make a change edit the existing answer

Gordon Linoff Over a year ago

@WatcharinChimmachuy . . . This answer should not be downvoted because you did not tag the your question correctly.

Collectives™ on Stack Overflow

SQL oracle : Group and Count data

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related