boon86
asked on
SQL: Query Question Again, calculate match with age and total
I have following table :
Table 1: PROFAIL
Table 2 : MATCH
SQL Dump as follow:
I currently have query for:
get age between 2 and 6
for Result A
Result B:
Problem : How do i combine result A and B into 1 query and able to get the result as picture below togather with total:
Thanks
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
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
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");
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'
)
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'
)
Problem : How do i combine result A and B into 1 query and able to get the result as picture below togather with total:
Thanks
ASKER
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'
)
actually, I don't care what your A and B queries are,
what do you want the results to be?
what do you want the results to be?
ASKER
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...
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...
what are the numberic values you are expecting for each row/column?
ASKER
The numeric values should be the number of children with corresponding age, gender and race
I mean, show me the actual values you are expecting
1, 0, 3, 5, 2, 7, 1, ....
0, 2, 4, 8, 1, 3, 6, ....
1, 0, 3, 5, 2, 7, 1, ....
0, 2, 4, 8, 1, 3, 6, ....
ASKER
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?
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?
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
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
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
ASKER
Lowfat,
full outer join did not working:
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
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(SYSDA TE, birth_date) / 12) BETWEEN lo AND hi
GROUP BY ROLLUP(lo)
ORDER BY GROUPING_ID(lo), lo)
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(SYSDA
GROUP BY ROLLUP(lo)
ORDER BY GROUPING_ID(lo), lo)
more like this
you didn't complete the statement correctly... or substitute in a value for "type"
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
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?
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?
ASKER
sdstuber, thanks for the outstanding query, it work
perfect. but i dont understand where did you get:
lo
hi
DUAL
perfect. but i dont understand where did you get:
lo
hi
DUAL
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"
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"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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' ?
FROM (SELECT DECODE(GROUPING_ID(lo), 1, 'OVERALL TOTAL', lo) age_group,
the above "lo" can replace with integer '0' ?
also note, your results are blank, do you want the number of children that fall into each category?