Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sql: How to add total for children and adult?

Posted on 2011-09-04
10
Medium Priority
?
633 Views
Last Modified: 2012-08-13
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
Comment
Question by:boon86
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 21

Expert Comment

by:oleggold
ID: 36480617
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
 
LVL 7

Author Comment

by:boon86
ID: 36480652
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
 
LVL 7

Author Comment

by:boon86
ID: 36482143
bump
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 36483177
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
 
LVL 7

Author Comment

by:boon86
ID: 36483288
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
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 36483357
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 36484606
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
 
LVL 7

Author Comment

by:boon86
ID: 36484659
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 36486128
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
 
LVL 7

Author Comment

by:boon86
ID: 36486432
yes work perfect!!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question