Calculation Percentage

[Trybbe]

Hi! Guys

Please Help I need to calculate percetages of my employee transfers Grouped by gender and race.

I have a query that goes like so:
SELECT Cast(COUNT(EarningRange)as Float) AS ER, Race, Gender, CASE WHEN TransferType = 'TransferIn' THEN 'Transfers' END AS HeadCount
FROM headcountdec
WHERE (Period BETWEEN @StartPeriod AND @EndPeriod) and (TransferType = 'TransferIn') AND (headcount = 'TransferCluster')
GROUP BY TransferType, EarningRange, EE_OCCUP_LEVELS, Race, Gender, headcount
Order BY RAce, Gender

The calculated field here is ER, I need to take that field as a calculated field and divide by sum(ER).

Please help

 

[PWise]

try

SELECT Cast(COUNT(EarningRange)as Float) AS ER, sum(er) as SumER,Cast(COUNT(EarningRange)as Float) / sum(er) as PercentageER, Race, Gender, CASE WHEN TransferType = 'TransferIn' THEN 'Transfers' END AS HeadCount
FROM headcountdec
WHERE (Period BETWEEN @StartPeriod AND @EndPeriod) and (TransferType = 'TransferIn') AND (headcount = 'TransferCluster')
GROUP BY TransferType, EarningRange, EE_OCCUP_LEVELS, Race, Gender, headcount
Order BY RAce, Gender

 

[Trybbe]

Hi! Thanks for the reply

The problem is that Earningranges is a text field which states the pay range of the employee hence I first need to count then use the results.

Witha that in mind I have decided to create a table from this query then use the resultant table to do my calculations.

Thanks a lot for your assistance