Home » SQL & PL/SQL » SQL & PL/SQL » Group by Multiple Values (Oracle 11.2.0.3 Windows)
Group by Multiple Values [message #655781] |
Mon, 12 September 2016 16:45 |
Duane
Messages: 557 Registered: December 2002
|
Senior Member |
|
|
I'm trying to figure this out. What I'm trying to do is count by group but also combined groups. How many are U - UGRD, G - GRAD, D - DR, U and G - anyone who is UGRAD and GRAD and anyone who is U, G and D - anyone who is UGRAD, GRAD and DR.
So, my output may be something like this (actual test case will be different):
U = 10
G = 5
D = 5
U/G = 4
U/G/D = 2
CREATE TABLE TEMP_DUANE_DEGREE_LEVEL
(
ID_NUMBER NUMBER,
DEGREE_LEVEL VARCHAR2(1 BYTE)
)
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(10, 'U');
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(20, 'U');
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(20, 'G');
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(40, 'D');
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(50, 'U');
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(50, 'G');
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(50, 'D');
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(80, 'D');
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(90, 'U');
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(100, 'U');
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(90, 'G');
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(120, 'U');
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(120, 'G');
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(140, 'U');
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(140, 'G');
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(160, 'U');
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(140, 'D');
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(180, 'D');
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(190, 'U');
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(200, 'U');
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(210, 'G');
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(220, 'G');
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(230, 'U');
Insert into TEMP_DUANE_DEGREE_LEVEL
(ID_NUMBER, DEGREE_LEVEL)
Values
(240, 'D');
COMMIT;
Output would be something like this:
DEGREE_LEVEL COUNT
U 10
G 5
D 5
U/G 4
U/G/D 2
NOTE: These counts do not match my test data. I only wanted to show what the output should be with dummy counts.
|
|
|
Re: Group by Multiple Values [message #655782 is a reply to message #655781] |
Mon, 12 September 2016 19:08 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
SQL> select *
2 from temp_duane_degree_level
3 order by degree_level desc,
4 id_number
5 /
ID_NUMBER DEGREE_LEVEL
---------- ---------------
10 U
20 U
50 U
90 U
100 U
120 U
140 U
160 U
190 U
200 U
230 U
ID_NUMBER DEGREE_LEVEL
---------- ---------------
20 G
50 G
90 G
120 G
140 G
210 G
220 G
40 D
50 D
80 D
140 D
ID_NUMBER DEGREE_LEVEL
---------- ---------------
180 D
240 D
24 rows selected.
SQL> with t1 as (
2 select 'U' degree_level from dual union all
3 select 'G' from dual union all
4 select 'D' from dual union all
5 select 'U/G' from dual union all
6 select 'U/G/D' from dual
7 ),
8 t2 as (
9 select id_number,
10 listagg(degree_level,'/') within group(order by degree_level desc) degree_level
11 from temp_duane_degree_level
12 group by id_number
13 )
14 select t1.degree_level,
15 count(t2.id_number) cnt
16 from t1,
17 t2
18 where instr('/' || t2.degree_level || '/','/' || t1.degree_level || '/') != 0
19 group by t1.degree_level
20 order by length(t1.degree_level),
21 t1.degree_level desc
22 /
DEGREE_LEVEL CNT
--------------- ----------
U 11
G 7
D 6
U/G 5
U/G/D 2
SQL>
SY.
|
|
|
Goto Forum:
Current Time: Thu Mar 28 20:26:45 CDT 2024
|