August 11, 2014 at 8:23 am
We are developing a query that will count the number of employees enrolled in a certain benefit id. That part is working fine but now we want to have a percentage filed that will use the count field divided by the sum of the count to provide the percentage of enrollment in that benefit. Below is the query that works perfectly to display the count of employees.
SELECT
ae.BenId,
ae.OptionId,
COUNT(ae.PersonId) AS 'Total Employee Enrollment'
FROM util_active_elections ae
JOIN cdm_employment_his eh ON ae.PersonId = eh.PersonId
WHERE ae.BenId IN (1,2,3,4,10,41,44,45,46,48,49,93) AND eh.UnionCode = 'NONE'
GROUP by ae.benid,ae.OptionId
ORDER by ae.BenId,ae.OptionId
August 11, 2014 at 8:31 am
SELECT
ae.BenId,
ae.OptionId,
COUNT(ae.PersonId) AS 'Total Employee Enrollment'
,COUNT(ae.PersonId)/(ca.TotalCount*1.0) as PercentEnrolled
FROM util_active_elections ae
INNER JOIN cdm_employment_his eh
ON ae.PersonId = eh.PersonId
CROSS APPLY (SELECT COUNT(ae.PersonId) AS TotalCount
FROM util_active_elections ae
JOIN cdm_employment_his eh
ON ae.PersonId = eh.PersonId ) ca
WHERE ae.BenId IN (1,2,3,4,10,41,44,45,46,48,49,93) AND eh.UnionCode = 'NONE'
GROUP by ae.benid,ae.OptionId
ORDER by ae.BenId,ae.OptionId;
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 11, 2014 at 8:37 am
I believe that will get me the correct answer now I just need to get the full percentage so I should just multiple by 100 correct?
August 11, 2014 at 8:47 am
Also i need to clarify that the percent calculation needs to multiple against the sum of the count of employees by benefit. So for example Benid 1 may have a Sum of 1000 but Benid1 and optionID 1 may have the count of 50, then the percent should be 50% as the sum is only for the total benid 1.
August 11, 2014 at 8:50 am
tstagliano (8/11/2014)
Also i need to clarify that the percent calculation needs to multiple against the sum of the count of employees by benefit. So for example Benid 1 may have a Sum of 1000 but Benid1 and optionID 1 may have the count of 50, then the percent should be 50% as the sum is only for the total benid 1.
According to your statement there, the percent should be 5% and not 50%.
As for the sum and calculations, I think you need to provide sample data and sample output at this point.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply