June 3, 2008 at 6:37 am
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
June 3, 2008 at 8:06 am
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).
June 3, 2008 at 11:10 am
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
June 3, 2008 at 1:46 pm
You're welcome! 🙂
June 4, 2008 at 12:45 pm
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
May 12, 2020 at 6:02 pm
Wonderful! It works, thank you!
November 10, 2021 at 2:52 pm
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