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.
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:
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
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 ainner join(select cCount=count(course), course from Grades group by course) w on w.Course=a.courseGROUP BY a.Course, a.Grade, w.cCountorder 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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply