Hi Tom,
I have a PATIENT table with (fName, lName .... ETHNICITY, GENDER.)
Another table XXX with some medical data.
I want to get the % of HispanicFemales% of total record in xxx table and the HispanicFemales% of Hispanic Ethnic in xxx table and the HispanicFemales% of All Females in xxx Table.I issue the following SELECT Statement:
SELECT P.GENDER,
COUNT ( * ) Total,
ROUND (ratio_to_report (COUNT (* )) OVER () * 100, 2)
HispanicFemale_Pct
FROM PATIENT P, xxx X
WHERE P.GENDER IS NOT NULL
AND P.MRN = X.MRN
AND P.ETHNICITY = 'Hispanic'
AND P.GENDER = 'Female'
GROUP BY P.GENDER;
This produced the Following:
GENDER Total HispanicFemale_Pct
Female 3 100
It calculated the % of itself.
My SQL will be inside java code.
How do I go for this requirement.
Thanks in Advance
Your where clause has this:
AND P.ETHNICITY = 'Hispanic'
AND P.GENDER = 'Female'
So you're only counting Hispanic Females! If you want to get the % of these across the whole data set, you need to remove these restrictions from your where clause:
with rws as (
select 'M' gender, 'Caucasian' ethnicity from dual union all
select 'F' gender, 'Caucasian' ethnicity from dual union all
select 'F' gender, 'Caucasian' ethnicity from dual union all
select 'M' gender, 'Hispanic' ethnicity from dual union all
select 'F' gender, 'Hispanic' ethnicity from dual
)
select gender, ethnicity, count(*),
round(ratio_to_report (count (* )) over () * 100, 2) pct
from rws
where gender = 'F'
and ethnicity = 'Hispanic'
group by gender, ethnicity;
GENDER ETHNICITY COUNT(*) PCT
F Hispanic 1 100
with rws as (
select 'M' gender, 'Caucasian' ethnicity from dual union all
select 'F' gender, 'Caucasian' ethnicity from dual union all
select 'F' gender, 'Caucasian' ethnicity from dual union all
select 'M' gender, 'Hispanic' ethnicity from dual union all
select 'F' gender, 'Hispanic' ethnicity from dual
)
select gender, ethnicity, count(*),
round(ratio_to_report (count (* )) over () * 100, 2) pct
from rws
group by gender, ethnicity;
GENDER ETHNICITY COUNT(*) PCT
F Caucasian 2 40
F Hispanic 1 20
M Hispanic 1 20
M Caucasian 1 20
For further details on ratio_to_report, check out Connor's video:
https://www.youtube.com/watch?v=KnrDtz7IBwE