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 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply