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.