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

1234567 A

1234567 A

1234567 B

1234567 C

5555555 A

5555555 B

5555555 B

5555555 B

I am looking to get results similar to:

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:

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:

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.

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)