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 Go to next message
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 Go to previous message
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.
Previous Topic: Improve nested Query, if possible
Next Topic: When we get the "Outbound Variable" Error in PLSQL
Goto Forum:
  


Current Time: Thu Mar 28 20:26:45 CDT 2024