Hello Team
is it possible to perform below task with one sql query?
There is a requirement where gender percentage need to calculate based on region as below
Actual input:
country state region Gender Ppl_Count
US NY T1 F 2
US NY T2 M 1
US NY T1 M 3
US NY T2 F 3
excepted output:
country,
state, -- Group to getting the total count and again group by gender to get respective count
region
____|______
| |
M% F%
sum(M)/Total sum(F)/Total
country state region F% M%
US NY T1 2/5 3/5
US NY T2 1/4 3/4
It looks like you want to PIVOT. This can convert the totals for each gender from rows to columns.
For example:
create table t (
country varchar2(2),
state varchar2(2),
region varchar2(2),
gender varchar2(1),
ppl_count integer
);
insert into t values ( 'US', 'NY', 'T1', 'F', 2 );
insert into t values ( 'US', 'NY', 'T2', 'M', 1 );
insert into t values ( 'US', 'NY', 'T1', 'M', 3 );
insert into t values ( 'US', 'NY', 'T2', 'F', 3 );
select country, state, region,
f_sm, m_sm
from t
pivot (
sum ( ppl_count ) sm
for gender in ( 'M' M, 'F' F )
);
COUNTRY STATE REGION F_SM M_SM
US NY T1 2 3
US NY T2 3 1 To get the percentages from there, divide the individual totals by the sum of both genders.
For more on pivoting, see
https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot