Solved

Sql: How to add total for children and adult?

Posted on 2011-09-04
10
612 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
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 21

Expert Comment

by:oleggold
Comment Utility
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
Comment Utility
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
Comment Utility
bump
0
 
LVL 10

Expert Comment

by:OnALearningCurve
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 10

Expert Comment

by:OnALearningCurve
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility
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 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
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
Comment Utility
yes work perfect!!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now