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/