# Calculate Percentage using GROUP BY

• I have a table that contains data in 2 columns that I need to calculate percentages on. An example of the data is as follows:

1234567 A

1234567 A

1234567 B

1234567 C

5555555 A

5555555 B

5555555 B

5555555 B

I am looking to get results similar to:

1234567 A 50%

1234567 B 25%

1234567 C 25%

5555555 A 25%

5555555 B 75%

My current query for giving me totals is as follows:

GROUP BY Course, Grade

This gives me my totals by grade, but I am having trouble figuring out the percentage calculation in the query.

Any help would be greatly appreciated.

Thanks!

Jody

• Do a subquery to get your total number of grades per course and divide each grade counts per course (already have) by this to get percentage:

FROM Course

WHERE Course = c.Course (from main query that calculates each grade count per course).

• Thank you! That got me going in the right direction. Here is what I ended up with that works.

SELECT i.Course, i.Grade, CONVERT(decimal(5, 2), COUNT(i.Grade)) / CONVERT(decimal(5, 2), (SELECT COUNT([Grade]) AS 'TotalGrades' FROM Grades WHERE (Course = i.Course))) * 100 AS 'Percentage'

GROUP BY i.Course, i.Grade

Jody

• You're welcome! 🙂

• Another solution:

`SELECT a.Course, a.Grade,`

`COUNT(a.Grade) as 'TotalGrades',`

`pt= convert(varchar(50),convert(int, COUNT(a.Grade)*100/w.cCount)) + '%'`

`FROM Grades a`

`inner join`

`(`

`select cCount=count(course), course from Grades group by course`

`) w on w.Course=a.course`

`GROUP BY a.Course, a.Grade, w.cCount`

`order by a.Course, a.Grade`

• Wonderful! It works, thank you!

• Thanks everyone. The outline of the problem and the solution provided helped me to solve my scenario.

I have variable number of tasks associated with a job card and the new report needs to be able to provide the end-user with a % complete of the tasks per job card.

Much obliged!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply