• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 643
  • Last Modified:

Sql: How to add total for children and adult?

Hello expert,


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  | Local

Open in new window




Table 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    | Bad

Open in new window


SQL 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");

Open in new window


Currently, my output is as follow :-
 current output
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)

Open in new window


Problem :  How to add the total for children and adult?

Output I want is as shown in the image below:-
 output wanted





0
boon86
Asked:
boon86
  • 5
  • 2
  • 2
  • +1
1 Solution
 
oleggoldCommented:
You need to add  another level :
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,
0
 
boon86Author Commented:
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)

Open in new window

0
 
boon86Author Commented:
bump
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
OnALearningCurveCommented:
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.
0
 
boon86Author Commented:
Hi Mar,

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)

Open in new window

0
 
OnALearningCurveCommented:
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.
0
 
sdstuberCommented:
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.

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)

Open in new window

0
 
boon86Author Commented:
current output
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?
0
 
sdstuberCommented:
try this...

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_total_m,
       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_total_m,
       b_total_f
  FROM (SELECT lo,
               CASE
                   WHEN logroupid = 1 AND cagroupid = 1 THEN 'OVERALL TOTAL'
                   WHEN logroupid = 1 AND cagroupid = 0 THEN 'TOTAL ' || child_adult
                   ELSE TO_CHAR(lo)
               END
                   age_group,
               logroupid,
               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 lo,
                       GROUPING_ID(lo) logroupid,
                       GROUPING_ID(CASE WHEN lo < 19 THEN 'CHILDREN' ELSE 'ADULTS' END) cagroupid,
                       CASE WHEN lo < 19 THEN 'CHILDREN' ELSE 'ADULTS' 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 'ADULTS' END))
         WHERE child_adult IS NOT NULL OR logroupid = 1
        ORDER BY child_adult DESC NULLS LAST, lo NULLS LAST)

Open in new window

0
 
boon86Author Commented:
yes work perfect!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now