Calculate Percentage using GROUP BY

  • Jody Chancey

    Old Hand

    Points: 328

    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.

    Thanks!

    Jody

  • cy-dba

    SSCarpal Tunnel

    Points: 4149

    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).

  • Jody Chancey

    Old Hand

    Points: 328

    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

    Jody

  • cy-dba

    SSCarpal Tunnel

    Points: 4149

    You're welcome! 🙂

  • johnsql-193053

    SSCommitted

    Points: 1657

    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

  • amitsharma

    Newbie

    Points: 1

    Wonderful! It works, thank you!

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

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