Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Group By ROLLUP

Roxy rollersOct 22 2021

Hello folks,
I am trying to do a SUM at the end of a group of Participants and finally a grant total. The grouping is by Ethnicity and I want Sub-totals and Totals. I believe this can be achieved by the Group by ROLLUP option. I am including my database version.
Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Script for Table creation:

CREATE TABLE DEMOGRAPHICS (PART_ID VARCHAR2(4), ETHNICITY VARCHAR2(50));
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1073','Asian');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1120','Other');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1139','Black');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1016','Black');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1021','Black');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1083','Black');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1006','Black');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1079','Asian');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1050','Other');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1051','Latino');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1055','Black');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1220','Latino');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1192','Latino');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1223','Latino');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1184','Latino');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1169','Latino');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1199','Asian');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1152','White');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1153','White');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1161','White');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1185','White');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1195','White');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1207','White');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1208','White');
Insert into DEMOGRAPHICS (PART_ID,ETHNICITY) values ('1206','White');

My SQL:

select CASE WHEN ord_num = 1 THEN ethnicity ELSE NULL END ethnicity, part_id
from
(
 select ethnicity, part_id,
        RANK() OVER (PARTITION BY ethnicity order by part_id) ord_num
 from
 (
  select ethnicity,
      CASE
       WHEN ethnicity = 'Asian'  THEN 1
       WHEN ethnicity = 'Black'  THEN 2
       WHEN ethnicity = 'Latino' THEN 3
       WHEN ethnicity = 'White'  THEN 4
       WHEN ethnicity = 'Other'  THEN 5
      END ethnicity_order_id,
      part_id
  from  demographics
 )
 order by ethnicity_order_id, part_id
);

My output should be:

Asian	1073
	1079
	1199
Sub-T      3
Black	1006
	1016
	1021
	1055
	1083
	1139
Sub-T      6
Latino	1051
	1169
	1184
	1192
	1220
	1223
Sub-T     6
White	1152
	1153
	1161
	1185
	1195
	1206
	1207
	1208
Sub-T      8
Other	1050
	1120
Sub-T      2

Total     25

Thanks in advance.

This post has been answered by Roxy rollers on Oct 22 2021
Jump to Answer
Comments
Post Details
Added on Oct 22 2021
3 comments
235 views