Solved

Sql: How to add total for children and adult?

Posted on 2011-09-04
10
620 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
Technology Partners: 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 500 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

734 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