September 1, 2016 at 6:20 am
Hi,
I am trying to calculate the proportion of records by City and YearMonth that have Male in the Gender column. I include code below that generates the input data set and desired output dataset.
The desired output would, for example, calculate proportion male for London 2015-04 as 10/15 i.e. 0.67.
Can any one help please?
Thanks,
Quentin
--create source table (this is in reality actually the result set of another query)
Declare @t table(City varchar(6),YearMonth varchar(7),Gender varchar(7), Records int)
insert into @t values
('London','2015-04','Male',10),
('London','2015-04','Female',5),
('Madrid','2015-04','Male',12),
('Madrid','2015-04','Female',20)
--view source data
Select * from @t
/*
Desired code ....
(sum of records where Gender=Male) / (Sum of all Records) for each city and YearMonth
i.e. London 2015-04 would be 10/15 i.e. 0.67 and Madrid 2015-04 would be 12/20 i.e. 0.60
*/
--Returns
Declare @return table(City varchar(6),YearMonth varchar(7),Gender varchar(7), Proportion Float)
insert into @return values
('London','2015-04','Male',0.67),
('Madrid','2015-04','Male',0.6)
Select * from @return
September 1, 2016 at 7:24 am
What a great job posting ddl, sample data and the desired output!!! When you post all the details this makes it so much easier to help. Thank you!!
The query for this is pretty straight forward aggregation. The challenge though is that you have to keep in mind integer division.
select SUM(case when Gender = 'Male' then 1.0 else 0 end)/SUM(Records)
, City
from @t
group by City, YearMonth
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 1, 2016 at 7:50 am
@sean - Thanks for the swift response.
This worked when i put in into the query which selected the source data used for the table i presented (which was the source for another query). The case statement had to work at the record level of that query.
Sorry i wasn't clearer.
Quentin
September 1, 2016 at 8:26 am
quentin.harris (9/1/2016)
@Sean - Thanks for the swift response.This worked when i put in into the query which selected the source data used for the table i presented (which was the source for another query). The case statement had to work at the record level of that query.
Sorry i wasn't clearer.
Quentin
Was pretty clear to me. 😀 Glad that worked for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 1, 2016 at 10:11 am
am I being a bit stupid here....but given the following I get the same results for each city ???
shouldnt it
London 0.75
Madrid 0.25
Declare @t table(City varchar(6),YearMonth varchar(7),Gender varchar(7), Records int)
insert into @t values
('London','2015-04','Male',3),
('London','2015-04','Female',1),
('Madrid','2015-04','Male',1),
('Madrid','2015-04','Female',3)
select SUM(case when Gender = 'Male' then 1.0 else 0 end)/SUM(Records)
, City
from @t
group by City, YearMonth
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 1, 2016 at 10:25 am
J Livingston SQL (9/1/2016)
am I being a bit stupid here....but given the following I get the same results for each city ???shouldnt it
London 0.75
Madrid 0.25
Declare @t table(City varchar(6),YearMonth varchar(7),Gender varchar(7), Records int)insert into @t values
('London','2015-04','Male',3),
('London','2015-04','Female',1),
('Madrid','2015-04','Male',1),
('Madrid','2015-04','Female',3)
select SUM(case when Gender = 'Male' then 1.0 else 0 end)/SUM(Records)
, City
from @t
group by City, YearMonth
No you aren't being stupid at all. You pointed out a flaw in the logic. A slight change is all that is required to get 1 * Records since it seems the data is already aggregated.
Declare @t table(City varchar(6),YearMonth varchar(7),Gender varchar(7), Records int)
insert into @t values
('London','2015-04','Male',3),
('London','2015-04','Female',1),
('Madrid','2015-04','Male',1),
('Madrid','2015-04','Female',3)
select SUM(case when Gender = 'Male' then 1.0 * Records else 0 end)/SUM(Records)
, City
from @t
group by City, YearMonth
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply