October 11, 2010 at 11:49 pm
Hi
I'm trying to calculate a percentage for the number of times an item appears within a group (note - not a percentage for the group total to the grand total).
For example, in the data below I need to return the percentage of times 'No' appears per group.
Column1 Column 2
Group 1 No
Group 1 No
Group 1 Yes
Group 1 No
Group 2 Yes
Group 2 Yes
Group 2 No
Group 2 Yes
Group 2 Yes
Group 3 Yes
Group 3 No
Group 3 Yes
Group 3 No
Results to be:
Column 1 Column 2
Group 1 75%
Group 2 20%
Group 3 50%
This information will be appearing in a SSRS table so an alternative option would be getting two columns which show the count of Yes/No's per group and a second column which shows just the count of 'No's per group and I'll get the table to perform the percentage calc.
Many thanks
October 12, 2010 at 3:33 am
SELECT Column1
, perc = SUM(CASE WHEN Column2 = 'No' THEN 1.0 END)/ COUNT(*) * 100.0
FROM dbo.percs_by_group
GROUP BY
Column1
October 12, 2010 at 8:46 pm
Hi hallidayd
That solution is beautiful in it's simplicity. I have adapted it to my query and it's working brilliantly.
Much appreaciated.
Paul
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply