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:

    Table Name: Grades

    CourseNum Grade

    1234567 A

    1234567 A

    1234567 B

    1234567 C

    5555555 A

    5555555 B

    5555555 B

    5555555 B

    I am looking to get results similar to:

    Course Grade Percentage

    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:

    SELECT Course, Grade, COUNT(Grade) as 'TotalGrades'

    FROM Grade

    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.



  • 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:

    Subquery: SELECT COUNT(Grade)

    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'

    FROM Grades i

    GROUP BY i.Course, i.Grade


  • 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