SQL: Query Question Again, calculate match with age and total

boon86
boon86 used Ask the Experts™
on
I have following table :

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

Open in new window



Table 2 : MATCH

MATCH_ID | PROFAIL_ID | MATCH | RESULT
--------------------------------------------
1	|  2	     |  A    | Good
2       |  4	     |  B    | Bad
3       |  3	     |  B    | Good 
4	|  2	     |  A    | Bad

Open in new window


SQL Dump as follow:
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 ('1', '2', 'A', 'Good');
INSERT INTO "MATCH" VALUES ('2', '4', 'B', 'Bad ');
INSERT INTO "MATCH" VALUES ('3', '3', 'B', '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');

-- ----------------------------
-- Indexes structure for table PROFAIL
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table "PROFAIL"
-- ----------------------------
ALTER TABLE "PROFAIL" ADD PRIMARY KEY ("PROFAIL_ID");

Open in new window



I currently have query for:

get age between 2 and 6
for Result A
SELECT
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' AND RACE = 'Malay' THEN PROFAIL END) AS GA1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' AND RACE = 'Malay' THEN PROFAIL END) AS GA2,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' AND RACE = 'Chinese' THEN PROFAIL END) AS GB1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' AND RACE = 'Chinese' THEN PROFAIL END) AS GB2,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' AND RACE = 'Indian' THEN PROFAIL END) AS GC1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' AND RACE = 'Indian' THEN PROFAIL END) AS GC2,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' AND RACE = 'Others' THEN PROFAIL END) AS GD1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' AND RACE = 'Others' THEN PROFAIL END) AS GD2,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' THEN PROFAIL END) AS J1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' THEN PROFAIL END) AS J2


FROM

(SELECT a.PROFAIL_ID as PROFAIL, GENDER , RACE, TO_CHAR (CURRENT_TIMESTAMP, 'YYYY') - TO_CHAR (BIRTH_DATE, 'YYYY') AS AGE FROM PROFAIL a, MATCH b
WHERE a.PROFAIL_ID = b.PROFAIL_ID
AND PROFAIL_TYPE ='A'
AND NATIONALITY = 'Local'
AND MATCH = 'A'
AND RESULT = 'Good'
)

Open in new window


Result B:
SELECT
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' AND RACE = 'Malay' THEN PROFAIL END) AS GA1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' AND RACE = 'Malay' THEN PROFAIL END) AS GA2,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' AND RACE = 'Chinese' THEN PROFAIL END) AS GB1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' AND RACE = 'Chinese' THEN PROFAIL END) AS GB2,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' AND RACE = 'Indian' THEN PROFAIL END) AS GC1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' AND RACE = 'Indian' THEN PROFAIL END) AS GC2,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' AND RACE = 'Others' THEN PROFAIL END) AS GD1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' AND RACE = 'Others' THEN PROFAIL END) AS GD2,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' THEN PROFAIL END) AS J1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' THEN PROFAIL END) AS J2


FROM

(SELECT a.PROFAIL_ID as PROFAIL, GENDER , RACE, TO_CHAR (CURRENT_TIMESTAMP, 'YYYY') - TO_CHAR (BIRTH_DATE, 'YYYY') AS AGE FROM PROFAIL a, MATCH b
WHERE a.PROFAIL_ID = b.PROFAIL_ID
AND PROFAIL_TYPE ='A'
AND NATIONALITY = 'Local'
AND MATCH = 'A'
AND RESULT = 'Good'
)

Open in new window


Problem : How do i combine result A and B into 1 query and able to get the result as picture below togather with total:

 table result
Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
what's the difference between A and B?

also note, your results are blank,  do you want the number of children that fall into each category?

Author

Commented:
sorry, result B query is:

SELECT
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' AND RACE = 'Malay' THEN PROFAIL END) AS HA1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' AND RACE = 'Malay' THEN PROFAIL END) AS HA2,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' AND RACE = 'Chinese' THEN PROFAIL END) AS HB1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' AND RACE = 'Chinese' THEN PROFAIL END) AS HB2,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' AND RACE = 'Indian' THEN PROFAIL END) AS HC1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' AND RACE = 'Indian' THEN PROFAIL END) AS HC2,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' AND RACE = 'Others' THEN PROFAIL END) AS HD1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' AND RACE = 'Others' THEN PROFAIL END) AS HD2,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' THEN PROFAIL END) AS HJ1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' THEN PROFAIL END) AS HJ2


FROM

(SELECT a.PROFAIL_ID as PROFAIL, GENDER , RACE, TO_CHAR (CURRENT_TIMESTAMP, 'YYYY') - TO_CHAR (BIRTH_DATE, 'YYYY') AS AGE FROM PROFAIL a, MATCH b
WHERE a.PROFAIL_ID = b.PROFAIL_ID
AND PROFAIL_TYPE ='A'
AND NATIONALITY = 'Local'
AND MATCH = 'A'
AND RESULT = 'Bad'
)

Open in new window

Most Valuable Expert 2011
Top Expert 2012

Commented:
actually, I don't care what your A and B queries are,

 what do you want the results to be?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
For Result A, should display the Result = Good depends on age, race and gender
by counting on number of Profail_Id involved in that condition.

For Result B, should display the Result = Bad depends on age, race and gender
by counting on number of Profail_Id involved in that condition too.


FYI : GA1 is column for Malay, Male
        GA2 is column for Malay, Female
        GB1 is column for Chinese, Male
        GB2 is column for Chinese, Female and so on...
Most Valuable Expert 2011
Top Expert 2012

Commented:
what are the numberic values you are expecting for each row/column?

Author

Commented:
The numeric values should be the number of children with corresponding age, gender and race
Most Valuable Expert 2011
Top Expert 2012

Commented:
I mean,  show me the actual values you are expecting

1, 0,  3, 5, 2, 7, 1, ....
0, 2, 4, 8, 1, 3, 6, ....

Author

Commented:
For row age between 2 and 6,

Result A should be this ,
 Result A


Result B should be this,
 Result B


This two results should appear in the same row
Most Valuable Expert 2011
Top Expert 2012

Commented:
your output table has 4 rows,  your queries only produce 1 row.

I don't need the queries you currently have
I won't use them  anyway since the results need to be combined

can you fill in your entire table with what you are expecting?
Top Expert 2011

Commented:
get admin to remove ms sql server from this its an oracle(?) query

you join the two queries via a full outer join...

select coalesce(a.type,b.type) as Type
    ,a.GA1
    ,a.GA2
     ,a.GB1
     ,a.GB2
,b.GA1
    ,b.GA2
     ,b.GB1
     ,b.GB2
,coalesce(a.j1,0)+coalesce(b.j1,0)
from (query a) as a
full outer join (queryb) as b
on a.type=b.type
order by 1


your query a and b  need to include a Type (probably the profail id?)

also you probably should set up the age range criteria in a table and use that in the a and b queries

Author

Commented:
output

Author

Commented:
Lowfat,

full outer join did not working:

[SQL] select coalesce(a.type,b.type) as Type
    ,a.GA1
    ,a.GA2 
     ,a.GB1 
     ,a.GB2
,b.GA1
    ,b.GA2 
     ,b.GB1 
     ,b.GB2
,coalesce(a.j1,0)+coalesce(b.j1,0) 
from 

(
SELECT
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' AND RACE = 'Malay' THEN PROFAIL END) AS GA1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' AND RACE = 'Malay' THEN PROFAIL END) AS GA2,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' AND RACE = 'Chinese' THEN PROFAIL END) AS GB1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' AND RACE = 'Chinese' THEN PROFAIL END) AS GB2,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' AND RACE = 'Indian' THEN PROFAIL END) AS GC1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' AND RACE = 'Indian' THEN PROFAIL END) AS GC2,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' AND RACE = 'Others' THEN PROFAIL END) AS GD1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' AND RACE = 'Others' THEN PROFAIL END) AS GD2,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' THEN PROFAIL END) AS J1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' THEN PROFAIL END) AS J2


FROM

(SELECT a.PROFAIL_ID as PROFAIL, GENDER , RACE, TO_CHAR (CURRENT_TIMESTAMP, 'YYYY') - TO_CHAR (BIRTH_DATE, 'YYYY') AS AGE FROM PROFAIL a, MATCH b
WHERE a.PROFAIL_ID = b.PROFAIL_ID
AND PROFAIL_TYPE ='A'
AND NATIONALITY = 'Local'
AND MATCH = 'A'
AND RESULT = 'Good'
)

) as a
full outer join 
(
SELECT
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' AND RACE = 'Malay' THEN PROFAIL END) AS HA1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' AND RACE = 'Malay' THEN PROFAIL END) AS HA2,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' AND RACE = 'Chinese' THEN PROFAIL END) AS HB1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' AND RACE = 'Chinese' THEN PROFAIL END) AS HB2,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' AND RACE = 'Indian' THEN PROFAIL END) AS HC1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' AND RACE = 'Indian' THEN PROFAIL END) AS HC2,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' AND RACE = 'Others' THEN PROFAIL END) AS HD1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' AND RACE = 'Others' THEN PROFAIL END) AS HD2,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='M' THEN PROFAIL END) AS HJ1,
COUNT(CASE WHEN AGE BETWEEN 2 AND 6 AND GENDER ='F' THEN PROFAIL END) AS HJ2


FROM

(SELECT a.PROFAIL_ID as PROFAIL, GENDER , RACE, TO_CHAR (CURRENT_TIMESTAMP, 'YYYY') - TO_CHAR (BIRTH_DATE, 'YYYY') AS AGE FROM PROFAIL a, MATCH b
WHERE a.PROFAIL_ID = b.PROFAIL_ID
AND PROFAIL_TYPE ='A'
AND NATIONALITY = 'Local'
AND MATCH = 'A'
AND RESULT = 'Bad'
)
) as b
on a.type=b.type
order by 1
[Err] ORA-00933: SQL command not properly ended

Open in new window

Most Valuable Expert 2011
Top Expert 2012

Commented:
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 = 'Good' THEN 1 END)
                     b_malay_m,
                 COUNT(CASE WHEN race = 'Malay' AND gender = 'F' AND result = 'Good' THEN 1 END)
                     b_malay_f,
                 COUNT(CASE WHEN race = 'Chinese' AND gender = 'M' AND result = 'Good' THEN 1 END)
                     b_chinese_m,
                 COUNT(CASE WHEN race = 'Chinese' AND gender = 'F' AND result = 'Good' THEN 1 END)
                     b_chinese_f,
                 COUNT(CASE WHEN race = 'Indian' AND gender = 'M' AND result = 'Good' THEN 1 END)
                     b_indian_m,
                 COUNT(CASE WHEN race = 'Indian' AND gender = 'F' AND result = 'Good' THEN 1 END)
                     b_indian_f,
                 COUNT(CASE WHEN race = 'Others' AND gender = 'M' AND result = 'Good' THEN 1 END)
                     b_others_m,
                 COUNT(CASE WHEN race = 'Others' AND gender = 'F' AND result = 'Good' 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)
                    ON FLOOR(MONTHS_BETWEEN(SYSDATE, birth_date) / 12) BETWEEN lo AND hi
        GROUP BY ROLLUP(lo)
        ORDER BY GROUPING_ID(lo), lo)
Top Expert 2011

Commented:
more like this

you didn't complete the statement correctly... or substitute in a value for "type"
select coalesce(a.lo,b.lo) as lo,coalesce(a.hi,b.hi) as hi
    ,a.GA1
    ,a.GA2 
     ,a.GB1 
     ,a.GB2
,b.hA1
    ,b.hA2 
     ,b.hB1 
     ,b.hB2
,coalesce(a.j1,0)+coalesce(b.hj1,0) 
from 

(
SELECT lo,hi,
COUNT(CASE WHEN AGE between lo and hi AND GENDER ='M' AND RACE = 'Malay' THEN PROFAIL END) AS GA1,
COUNT(CASE WHEN AGE between lo and hi AND GENDER ='F' AND RACE = 'Malay' THEN PROFAIL END) AS GA2,
COUNT(CASE WHEN AGE between lo and hi AND GENDER ='M' AND RACE = 'Chinese' THEN PROFAIL END) AS GB1,
COUNT(CASE WHEN AGE between lo and hi AND GENDER ='F' AND RACE = 'Chinese' THEN PROFAIL END) AS GB2,
COUNT(CASE WHEN AGE between lo and hi AND GENDER ='M' AND RACE = 'Indian' THEN PROFAIL END) AS GC1,
COUNT(CASE WHEN AGE between lo and hi AND GENDER ='F' AND RACE = 'Indian' THEN PROFAIL END) AS GC2,
COUNT(CASE WHEN AGE between lo and hi AND GENDER ='M' AND RACE = 'Others' THEN PROFAIL END) AS GD1,
COUNT(CASE WHEN AGE between lo and hi AND GENDER ='F' AND RACE = 'Others' THEN PROFAIL END) AS GD2,
COUNT(CASE WHEN AGE between lo and hi AND GENDER ='M' THEN PROFAIL END) AS J1,
COUNT(CASE WHEN AGE between lo and hi AND GENDER ='F' THEN PROFAIL END) AS J2


FROM

(SELECT a.PROFAIL_ID as PROFAIL, GENDER , RACE, TO_CHAR (CURRENT_TIMESTAMP, 'YYYY') - TO_CHAR (BIRTH_DATE, 'YYYY') AS AGE 
  FROM PROFAIL a, MATCH b,
  (SELECT 0 lo, 1 hi FROM DUAL
                            UNION ALL
                            SELECT 2, 6 FROM DUAL
                            UNION ALL
                            SELECT 7, 12 FROM DUAL) as c
WHERE a.PROFAIL_ID = b.PROFAIL_ID
AND PROFAIL_TYPE ='A'
AND NATIONALITY = 'Local'
AND MATCH = 'A'
AND RESULT = 'Good'
) as x
group by lo,hi
) as a
full outer join 
(
SELECT lo,hi
COUNT(CASE WHEN AGE between lo and hi AND GENDER ='M' AND RACE = 'Malay' THEN PROFAIL END) AS HA1,
COUNT(CASE WHEN AGE between lo and hi AND GENDER ='F' AND RACE = 'Malay' THEN PROFAIL END) AS HA2,
COUNT(CASE WHEN AGE between lo and hi AND GENDER ='M' AND RACE = 'Chinese' THEN PROFAIL END) AS HB1,
COUNT(CASE WHEN AGE between lo and hi AND GENDER ='F' AND RACE = 'Chinese' THEN PROFAIL END) AS HB2,
COUNT(CASE WHEN AGE between lo and hi AND GENDER ='M' AND RACE = 'Indian' THEN PROFAIL END) AS HC1,
COUNT(CASE WHEN AGE between lo and hi AND GENDER ='F' AND RACE = 'Indian' THEN PROFAIL END) AS HC2,
COUNT(CASE WHEN AGE between lo and hi AND GENDER ='M' AND RACE = 'Others' THEN PROFAIL END) AS HD1,
COUNT(CASE WHEN AGE between lo and hi AND GENDER ='F' AND RACE = 'Others' THEN PROFAIL END) AS HD2,
COUNT(CASE WHEN AGE between lo and hi AND GENDER ='M' THEN PROFAIL END) AS HJ1,
COUNT(CASE WHEN AGE between lo and hi AND GENDER ='F' THEN PROFAIL END) AS HJ2


FROM

(SELECT a.PROFAIL_ID as PROFAIL, GENDER , RACE, TO_CHAR (CURRENT_TIMESTAMP, 'YYYY') - TO_CHAR (BIRTH_DATE, 'YYYY') AS AGE
 FROM PROFAIL a, MATCH b, (SELECT 0 lo, 1 hi FROM DUAL
                            UNION ALL
                            SELECT 2, 6 FROM DUAL
                            UNION ALL
                            SELECT 7, 12 FROM DUAL) as c
WHERE a.PROFAIL_ID = b.PROFAIL_ID
AND PROFAIL_TYPE ='A'
AND NATIONALITY = 'Local'
AND MATCH = 'A'
AND RESULT = 'Bad'
) as x
group by lo,hi
) as b
on a.lo=b.lo
and a.hi=b.hi
order by 1

Open in new window

Most Valuable Expert 2011
Top Expert 2012

Commented:
as noted above  this question is zoned for Oracle and MS SQL Server
your previous question was zoned for Oracle and MySQL

the previous question is closed, but this one can still be changed.


my answers have been oracle specific,  what are the appropriate zones here?

Author

Commented:
sdstuber, thanks for the outstanding query, it work
perfect. but i dont understand where did you get:

lo
hi
DUAL


Most Valuable Expert 2011
Top Expert 2012

Commented:
dual is a built in table of oracle
it has one row of one column  but that's not important.
the utility of it is for including functions or constants where you don't really care about the contents of table or view

in this case  I wanted hi and lo values for the age ranges so, this


SELECT 0 lo, 1 hi FROM DUAL
 UNION ALL
SELECT 2, 6 FROM DUAL
 UNION ALL
SELECT 7, 12 FROM DUAL


lets me create data from "nothing"
Most Valuable Expert 2011
Top Expert 2012
Commented:
oops, I just noticed that I didn't put "Bad" into the B results

fixed below


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

Author

Commented:
If my start age range is nt start from zero,

FROM (SELECT   DECODE(GROUPING_ID(lo), 1, 'OVERALL TOTAL', lo) age_group,

the above "lo" can replace with integer '0' ?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial