Sql: How to add total for children and adult?
Hello expert,
Table 1: PROFAIL
Table 2 : MATCH
SQL Dump:-
Currently, my output is as follow :-
Below is my query :-
Problem : How to add the total for children and adult?
Output I want is as shown in the image below:-
Table 1: PROFAIL
PROFAIL_ID | NAME | GENDER | BIRTH_DATE | PROFAIL_TYPE | RACE | NATIONALITY
--------------------------------------------------------------------------------------
1 | Andy | M |2009-03-12 00:00:00 | A | Chinese | Local
2 | Owen | M |2005-05-22 00:00:00 | A | Others | Local
3 | Abby | F |2004-02-15 00:00:00 | A | Malay | Local
4 | Mimi | F |2002-03-02 00:00:00 | A | Indian | Local
5 | Jason | M |1967-05-01 00:00:00 | A | Chinese | Local
6 | Mandy | F |1961-12-12 00:00:00 | A | Others | Local
7 | Candy | F |1961-12-12 00:00:00 | A | Indian | LocalTable 2 : MATCH
MATCH_ID | PROFAIL_ID | MATCH | RESULT
--------------------------------------------
1 | 2 | A | Good
2 | 4 | A | Bad
3 | 3 | A | Good
4 | 2 | A | Bad
5 | 5 | A | Good
6 | 7 | A | Good
7 | 6 | A | BadSQL Dump:-
-- ----------------------------
-- Table structure for "MATCH"
-- ----------------------------
DROP TABLE "MATCH";
CREATE TABLE "MATCH" (
"MATCH_ID" VARCHAR2(16 BYTE) NULL ,
"PROFAIL_ID" VARCHAR2(16 BYTE) NULL ,
"MATCH" VARCHAR2(16 BYTE) NULL ,
"RESULT" VARCHAR2(16 BYTE) NULL
)
LOGGING
NOCOMPRESS
NOCACHE
;
-- ----------------------------
-- Records of MATCH
-- ----------------------------
INSERT INTO "MATCH" VALUES ('5', '5', 'A', 'Good ');
INSERT INTO "MATCH" VALUES ('6', '7', 'A', 'Good');
INSERT INTO "MATCH" VALUES ('7', '6', 'A', 'Bad');
INSERT INTO "MATCH" VALUES ('1', '2', 'A', 'Good');
INSERT INTO "MATCH" VALUES ('2', '4', 'A', 'Bad ');
INSERT INTO "MATCH" VALUES ('3', '3', 'A', 'Good');
INSERT INTO "MATCH" VALUES ('4', '2', 'A', 'Bad');
-- ----------------------------
-- Table structure for "PROFAIL"
-- ----------------------------
DROP TABLE "PROFAIL";
CREATE TABLE "PROFAIL" (
"PROFAIL_ID" VARCHAR2(16 BYTE) NOT NULL ,
"NAME" VARCHAR2(200 BYTE) NULL ,
"NATIONALITY" VARCHAR2(200 BYTE) NULL ,
"GENDER" VARCHAR2(16 BYTE) NULL ,
"BIRTH_DATE" DATE NULL ,
"PROFAIL_TYPE" VARCHAR2(16 BYTE) NULL ,
"RACE" VARCHAR2(16 BYTE) NULL
)
LOGGING
NOCOMPRESS
NOCACHE
;
-- ----------------------------
-- Records of PROFAIL
-- ----------------------------
INSERT INTO "PROFAIL" VALUES ('1', 'Andy', 'Local', 'M', TO_DATE('2009-03-12 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'A', 'Chinese');
INSERT INTO "PROFAIL" VALUES ('2', 'Owen', 'Local', 'M', TO_DATE('2005-05-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'A', 'Others');
INSERT INTO "PROFAIL" VALUES ('3', 'Abby', 'Local', 'F', TO_DATE('2004-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'A', 'Malay');
INSERT INTO "PROFAIL" VALUES ('4', 'Mimi', 'Local', 'F', TO_DATE('2002-03-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'A', 'Indian');
INSERT INTO "PROFAIL" VALUES ('5', 'Jason', 'Local', 'M', TO_DATE('1967-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'A', 'Chinese');
INSERT INTO "PROFAIL" VALUES ('6', 'Mandy', 'Local', 'F', TO_DATE('1961-12-12 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'A', 'Others');
INSERT INTO "PROFAIL" VALUES ('7', 'Candy', 'Local', 'F', TO_DATE('1961-12-12 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'A', 'Indian');
-- ----------------------------
-- Indexes structure for table PROFAIL
-- ----------------------------
-- ----------------------------
-- Primary Key structure for table "PROFAIL"
-- ----------------------------
ALTER TABLE "PROFAIL" ADD PRIMARY KEY ("PROFAIL_ID");Currently, my output is as follow :-
Below is my query :-
SELECT age_group,
a_malay_m,
a_malay_f,
a_chinese_m,
a_chinese_f,
a_indian_m,
a_indian_f,
a_others_m,
a_others_f,
(a_malay_m + a_chinese_m + a_indian_m + a_others_m) a_total_m,
(a_malay_f + a_chinese_f + a_indian_f + a_others_f) a_total_f,
b_malay_m,
b_malay_f,
b_chinese_m,
b_chinese_f,
b_indian_m,
b_indian_f,
b_others_m,
b_others_f,
(b_malay_m + b_chinese_m + b_indian_m + b_others_m) b_total_m,
(b_malay_f + b_chinese_f + b_indian_f + b_others_f) b_total_f
FROM (SELECT DECODE(GROUPING_ID(lo), 1, 'OVERALL TOTAL', lo) age_group,
COUNT(CASE WHEN race = 'Malay' AND gender = 'M' AND result = 'Good' THEN 1 END)
a_malay_m,
COUNT(CASE WHEN race = 'Malay' AND gender = 'F' AND result = 'Good' THEN 1 END)
a_malay_f,
COUNT(CASE WHEN race = 'Chinese' AND gender = 'M' AND result = 'Good' THEN 1 END)
a_chinese_m,
COUNT(CASE WHEN race = 'Chinese' AND gender = 'F' AND result = 'Good' THEN 1 END)
a_chinese_f,
COUNT(CASE WHEN race = 'Indian' AND gender = 'M' AND result = 'Good' THEN 1 END)
a_indian_m,
COUNT(CASE WHEN race = 'Indian' AND gender = 'F' AND result = 'Good' THEN 1 END)
a_indian_f,
COUNT(CASE WHEN race = 'Others' AND gender = 'M' AND result = 'Good' THEN 1 END)
a_others_m,
COUNT(CASE WHEN race = 'Others' AND gender = 'F' AND result = 'Good' THEN 1 END)
a_others_f,
COUNT(CASE WHEN race = 'Malay' AND gender = 'M' AND result = 'Bad' THEN 1 END)
b_malay_m,
COUNT(CASE WHEN race = 'Malay' AND gender = 'F' AND result = 'Bad' THEN 1 END)
b_malay_f,
COUNT(CASE WHEN race = 'Chinese' AND gender = 'M' AND result = 'Bad' THEN 1 END)
b_chinese_m,
COUNT(CASE WHEN race = 'Chinese' AND gender = 'F' AND result = 'Bad' THEN 1 END)
b_chinese_f,
COUNT(CASE WHEN race = 'Indian' AND gender = 'M' AND result = 'Bad' THEN 1 END)
b_indian_m,
COUNT(CASE WHEN race = 'Indian' AND gender = 'F' AND result = 'Bad' THEN 1 END)
b_indian_f,
COUNT(CASE WHEN race = 'Others' AND gender = 'M' AND result = 'Bad' THEN 1 END)
b_others_m,
COUNT(CASE WHEN race = 'Others' AND gender = 'F' AND result = 'Bad' THEN 1 END)
b_others_f
FROM profail a
JOIN match b
ON a.profail_id = b.profail_id
AND profail_type = 'A'
AND nationality = 'Local'
AND match = 'A'
RIGHT JOIN (SELECT 0 lo, 1 hi FROM DUAL
UNION ALL
SELECT 2, 6 FROM DUAL
UNION ALL
SELECT 7, 12 FROM DUAL
UNION ALL
SELECT 13, 18 FROM DUAL
UNION ALL
SELECT 19, 45 FROM DUAL
UNION ALL
SELECT 46, 59 FROM DUAL
UNION ALL
SELECT 60, 100 FROM DUAL)
ON FLOOR(MONTHS_BETWEEN(SYSDATE, birth_date) / 12) BETWEEN lo AND hi
GROUP BY ROLLUP(lo)
ORDER BY GROUPING_ID(lo), lo)Problem : How to add the total for children and adult?
Output I want is as shown in the image below:-
ASKER
I had tried the code. I get this error :- [Err] ORA-00904: "AGE": invalid identifier
SELECT age_group,
a_malay_m,
a_malay_f,
a_chinese_m,
a_chinese_f,
a_indian_m,
a_indian_f,
a_others_m,
a_others_f,
(a_malay_m + a_chinese_m + a_indian_m + a_others_m) a_total_m,
(a_malay_f + a_chinese_f + a_indian_f + a_others_f) a_total_f,
b_malay_m,
b_malay_f,
b_chinese_m,
b_chinese_f,
b_indian_m,
b_indian_f,
b_others_m,
b_others_f,
(b_malay_m + b_chinese_m + b_indian_m + b_others_m) b_total_m,
(b_malay_f + b_chinese_f + b_indian_f + b_others_f) b_total_f
FROM (SELECT DECODE(GROUPING_ID(lo), 1, 'OVERALL TOTAL', lo) age_group,
DECODE(age_group, 1, 'child',2,'adult') age,
COUNT(CASE WHEN race = 'Malay' AND gender = 'M' AND result = 'Good' THEN 1 END)
a_malay_m,
COUNT(CASE WHEN race = 'Malay' AND gender = 'F' AND result = 'Good' THEN 1 END)
a_malay_f,
COUNT(CASE WHEN race = 'Chinese' AND gender = 'M' AND result = 'Good' THEN 1 END)
a_chinese_m,
COUNT(CASE WHEN race = 'Chinese' AND gender = 'F' AND result = 'Good' THEN 1 END)
a_chinese_f,
COUNT(CASE WHEN race = 'Indian' AND gender = 'M' AND result = 'Good' THEN 1 END)
a_indian_m,
COUNT(CASE WHEN race = 'Indian' AND gender = 'F' AND result = 'Good' THEN 1 END)
a_indian_f,
COUNT(CASE WHEN race = 'Others' AND gender = 'M' AND result = 'Good' THEN 1 END)
a_others_m,
COUNT(CASE WHEN race = 'Others' AND gender = 'F' AND result = 'Good' THEN 1 END)
a_others_f,
COUNT(CASE WHEN race = 'Malay' AND gender = 'M' AND result = 'Bad' THEN 1 END)
b_malay_m,
COUNT(CASE WHEN race = 'Malay' AND gender = 'F' AND result = 'Bad' THEN 1 END)
b_malay_f,
COUNT(CASE WHEN race = 'Chinese' AND gender = 'M' AND result = 'Bad' THEN 1 END)
b_chinese_m,
COUNT(CASE WHEN race = 'Chinese' AND gender = 'F' AND result = 'Bad' THEN 1 END)
b_chinese_f,
COUNT(CASE WHEN race = 'Indian' AND gender = 'M' AND result = 'Bad' THEN 1 END)
b_indian_m,
COUNT(CASE WHEN race = 'Indian' AND gender = 'F' AND result = 'Bad' THEN 1 END)
b_indian_f,
COUNT(CASE WHEN race = 'Others' AND gender = 'M' AND result = 'Bad' THEN 1 END)
b_others_m,
COUNT(CASE WHEN race = 'Others' AND gender = 'F' AND result = 'Bad' THEN 1 END)
b_others_f,
COUNT(CASE WHEN age = 'child' THEN 1 END)
child_total,
COUNT(CASE WHEN age = 'adult' THEN 1 END)
adult_total
FROM profail a
JOIN match b
ON a.profail_id = b.profail_id
AND profail_type = 'A'
AND nationality = 'Local'
AND match = 'A'
RIGHT JOIN (SELECT 0 lo, 1 hi FROM DUAL
UNION ALL
SELECT 2, 6 FROM DUAL
UNION ALL
SELECT 7, 12 FROM DUAL
UNION ALL
SELECT 13, 18 FROM DUAL
UNION ALL
SELECT 19, 45 FROM DUAL
UNION ALL
SELECT 46, 59 FROM DUAL
UNION ALL
SELECT 60, 100 FROM DUAL)
ON FLOOR(MONTHS_BETWEEN(SYSDATE, birth_date) / 12) BETWEEN lo AND hi
GROUP BY ROLLUP(lo)
ORDER BY GROUPING_ID(lo), lo)ASKER
bump
Hi boon86,
try replacing:
COUNT(CASE WHEN age= 'child' THEN 1 END)
child_total,
COUNT(CASE WHEN age= 'adult' THEN 1 END)
adult_total,
With:
COUNT(CASE WHEN age_group = 1 THEN 1 END)
child_total,
COUNT(CASE WHEN age_group = 2 THEN 1 END)
adult_total
Hope this helps,
Mar.
try replacing:
COUNT(CASE WHEN age= 'child' THEN 1 END)
child_total,
COUNT(CASE WHEN age= 'adult' THEN 1 END)
adult_total,
With:
COUNT(CASE WHEN age_group = 1 THEN 1 END)
child_total,
COUNT(CASE WHEN age_group = 2 THEN 1 END)
adult_total
Hope this helps,
Mar.
ASKER
Hi Mar,
it show:
[Err] ORA-00904: "AGE_GROUP": invalid identifier
below is my query replaced with your suggested:
it show:
[Err] ORA-00904: "AGE_GROUP": invalid identifier
below is my query replaced with your suggested:
SELECT age_group,
a_malay_m,
a_malay_f,
a_chinese_m,
a_chinese_f,
a_indian_m,
a_indian_f,
a_others_m,
a_others_f,
(a_malay_m + a_chinese_m + a_indian_m + a_others_m) a_total_m,
(a_malay_f + a_chinese_f + a_indian_f + a_others_f) a_total_f,
b_malay_m,
b_malay_f,
b_chinese_m,
b_chinese_f,
b_indian_m,
b_indian_f,
b_others_m,
b_others_f,
(b_malay_m + b_chinese_m + b_indian_m + b_others_m) b_total_m,
(b_malay_f + b_chinese_f + b_indian_f + b_others_f) b_total_f
FROM (SELECT DECODE(GROUPING_ID(lo), 1, 'OVERALL TOTAL', lo) age_group,
DECODE(age_group, 1, 'child',2,'adult') age_group,
COUNT(CASE WHEN race = 'Malay' AND gender = 'M' AND result = 'Good' THEN 1 END)
a_malay_m,
COUNT(CASE WHEN race = 'Malay' AND gender = 'F' AND result = 'Good' THEN 1 END)
a_malay_f,
COUNT(CASE WHEN race = 'Chinese' AND gender = 'M' AND result = 'Good' THEN 1 END)
a_chinese_m,
COUNT(CASE WHEN race = 'Chinese' AND gender = 'F' AND result = 'Good' THEN 1 END)
a_chinese_f,
COUNT(CASE WHEN race = 'Indian' AND gender = 'M' AND result = 'Good' THEN 1 END)
a_indian_m,
COUNT(CASE WHEN race = 'Indian' AND gender = 'F' AND result = 'Good' THEN 1 END)
a_indian_f,
COUNT(CASE WHEN race = 'Others' AND gender = 'M' AND result = 'Good' THEN 1 END)
a_others_m,
COUNT(CASE WHEN race = 'Others' AND gender = 'F' AND result = 'Good' THEN 1 END)
a_others_f,
COUNT(CASE WHEN race = 'Malay' AND gender = 'M' AND result = 'Bad' THEN 1 END)
b_malay_m,
COUNT(CASE WHEN race = 'Malay' AND gender = 'F' AND result = 'Bad' THEN 1 END)
b_malay_f,
COUNT(CASE WHEN race = 'Chinese' AND gender = 'M' AND result = 'Bad' THEN 1 END)
b_chinese_m,
COUNT(CASE WHEN race = 'Chinese' AND gender = 'F' AND result = 'Bad' THEN 1 END)
b_chinese_f,
COUNT(CASE WHEN race = 'Indian' AND gender = 'M' AND result = 'Bad' THEN 1 END)
b_indian_m,
COUNT(CASE WHEN race = 'Indian' AND gender = 'F' AND result = 'Bad' THEN 1 END)
b_indian_f,
COUNT(CASE WHEN race = 'Others' AND gender = 'M' AND result = 'Bad' THEN 1 END)
b_others_m,
COUNT(CASE WHEN race = 'Others' AND gender = 'F' AND result = 'Bad' THEN 1 END)
b_others_f,
COUNT(CASE WHEN age_group = 1 THEN 1 END)
child_total,
COUNT(CASE WHEN age_group = 2 THEN 1 END)
adult_total
FROM profail a
JOIN match b
ON a.profail_id = b.profail_id
AND profail_type = 'A'
AND nationality = 'Local'
AND match = 'A'
RIGHT JOIN (SELECT 0 lo, 1 hi FROM DUAL
UNION ALL
SELECT 2, 6 FROM DUAL
UNION ALL
SELECT 7, 12 FROM DUAL
UNION ALL
SELECT 13, 18 FROM DUAL
UNION ALL
SELECT 19, 45 FROM DUAL
UNION ALL
SELECT 46, 59 FROM DUAL
UNION ALL
SELECT 60, 100 FROM DUAL)
ON FLOOR(MONTHS_BETWEEN(SYSDATE, birth_date) / 12) BETWEEN lo AND hi
GROUP BY ROLLUP(lo)
ORDER BY GROUPING_ID(lo), lo)
Then I'm not sure sorry,
I think it is something to do with the DECODE fields you have but I don't have a lot of experience with queries using ROLLUP.
Sorry I can't be any more help.
I think it is something to do with the DECODE fields you have but I don't have a lot of experience with queries using ROLLUP.
Sorry I can't be any more help.
Try this...
the "trick" here is you need to use CUBE instead of ROLLUP, CUBE will generate extra summaries you're not interested in though, so we need to filter down to just what you're really looking for.
the "trick" here is you need to use CUBE instead of ROLLUP, CUBE will generate extra summaries you're not interested in though, so we need to filter down to just what you're really looking for.
SELECT *
FROM (SELECT age_group,
a_malay_m,
a_malay_f,
a_chinese_m,
a_chinese_f,
a_indian_m,
a_indian_f,
a_others_m,
a_others_f,
(a_malay_m + a_chinese_m + a_indian_m + a_others_m) a_total_m,
(a_malay_f + a_chinese_f + a_indian_f + a_others_f) a_total_f,
b_malay_m,
b_malay_f,
b_chinese_m,
b_chinese_f,
b_indian_m,
b_indian_f,
b_others_m,
b_others_f,
(b_malay_m + b_chinese_m + b_indian_m + b_others_m) b_total_m,
(b_malay_f + b_chinese_f + b_indian_f + b_others_f) b_total_f
FROM (SELECT DECODE(GROUPING_ID(lo), 1, 'OVERALL TOTAL', lo) age_group,
GROUPING_ID(lo) age_id,
CASE WHEN lo < 19 THEN 'Children' ELSE 'Adult' END child_adult,
COUNT(
CASE WHEN race = 'Malay' AND gender = 'M' AND result = 'Good' THEN 1 END
)
a_malay_m,
COUNT(
CASE WHEN race = 'Malay' AND gender = 'F' AND result = 'Good' THEN 1 END
)
a_malay_f,
COUNT(
CASE
WHEN race = 'Chinese' AND gender = 'M' AND result = 'Good' THEN 1
END
)
a_chinese_m,
COUNT(
CASE
WHEN race = 'Chinese' AND gender = 'F' AND result = 'Good' THEN 1
END
)
a_chinese_f,
COUNT(
CASE
WHEN race = 'Indian' AND gender = 'M' AND result = 'Good' THEN 1
END
)
a_indian_m,
COUNT(
CASE
WHEN race = 'Indian' AND gender = 'F' AND result = 'Good' THEN 1
END
)
a_indian_f,
COUNT(
CASE
WHEN race = 'Others' AND gender = 'M' AND result = 'Good' THEN 1
END
)
a_others_m,
COUNT(
CASE
WHEN race = 'Others' AND gender = 'F' AND result = 'Good' THEN 1
END
)
a_others_f,
COUNT(
CASE WHEN race = 'Malay' AND gender = 'M' AND result = 'Bad' THEN 1 END
)
b_malay_m,
COUNT(
CASE WHEN race = 'Malay' AND gender = 'F' AND result = 'Bad' THEN 1 END
)
b_malay_f,
COUNT(
CASE
WHEN race = 'Chinese' AND gender = 'M' AND result = 'Bad' THEN 1
END
)
b_chinese_m,
COUNT(
CASE
WHEN race = 'Chinese' AND gender = 'F' AND result = 'Bad' THEN 1
END
)
b_chinese_f,
COUNT(
CASE WHEN race = 'Indian' AND gender = 'M' AND result = 'Bad' THEN 1 END
)
b_indian_m,
COUNT(
CASE WHEN race = 'Indian' AND gender = 'F' AND result = 'Bad' THEN 1 END
)
b_indian_f,
COUNT(
CASE WHEN race = 'Others' AND gender = 'M' AND result = 'Bad' THEN 1 END
)
b_others_m,
COUNT(
CASE WHEN race = 'Others' AND gender = 'F' AND result = 'Bad' THEN 1 END
)
b_others_f
FROM profail a
JOIN match b
ON a.profail_id = b.profail_id
AND profail_type = 'A'
AND nationality = 'Local'
AND match = 'A'
RIGHT JOIN (SELECT 0 lo, 1 hi FROM DUAL
UNION ALL
SELECT 2, 6 FROM DUAL
UNION ALL
SELECT 7, 12 FROM DUAL
UNION ALL
SELECT 13, 18 FROM DUAL
UNION ALL
SELECT 19, 45 FROM DUAL
UNION ALL
SELECT 46, 59 FROM DUAL
UNION ALL
SELECT 60, 100 FROM DUAL)
ON FLOOR(MONTHS_BETWEEN(SYSDATE, birth_date) / 12) BETWEEN lo AND hi
GROUP BY CUBE(lo, CASE WHEN lo < 19 THEN 'Children' ELSE 'Adult' END))
WHERE child_adult IS NOT NULL OR age_group = 'OVERALL TOTAL'
ORDER BY child_adult DESC NULLS LAST, age_group)ASKER
The age arrangement for 13 and 2 is nt correct and how do I rename the OVERALL for children and adult to my own name?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes work perfect!!
DECODE(age_group, 1, 'child',2,'adult') age
...
then
COUNT(CASE WHEN age= 'child' THEN 1 END)
child_total,
COUNT(CASE WHEN age= 'adult' THEN 1 END)
adult_total,