Calculate Percentage using GROUP BY

Question

Calculate Percentage using GROUP BY

Jody Chancey

Old Hand

Points: 328
More actions
June 3, 2008 at 6:37 am

#72167

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

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

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

cy-dba SSCarpal Tunnel Points: 4179 More actions · Answer 1

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 More actions · Answer 2

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: 4179 More actions · Answer 3

cy-dba

SSCarpal Tunnel

Points: 4179

June 3, 2008 at 1:46 pm

#823849

You're welcome! 🙂

johnsql-193053 SSCommitted Points: 1657 More actions · Answer 4

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 More actions · Answer 5

amitsharma

Newbie

Points: 1

May 12, 2020 at 6:02 pm

#3750893

Wonderful! It works, thank you!

Staalbaard Newbie Points: 2 More actions · Answer 6

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!