Solved

Sql: How to add total for children and adult?

Posted on 2011-09-04
10
623 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
Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

724 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