Help Calculating Percentages

Question

Post reply

Help Calculating Percentages

Jody Chancey

Old Hand

Points: 328
More actions
November 28, 2008 at 7:53 am

#73116

I need help calculating percentages of values in one column grouped by another column. Here is some sample data:
CourseNum Grade
0500300 A
0500300 B
0500300 A
0500300 C
0600000 A
0600000 C
The results I am looking for would be:
CourseNum Grade Percentage
0500300 A 50%
0500300 B 25%
0500300 C 25%
0600000 A 50%
0600000 C 50%
I have a query that gives me the grades grouped by CourseNum:
SELECT CourseNum, Grade
CONVERT(decimal(6,2), COUNT(Grade)) AS 'Grade Count'
FROM TempGrades
GROUP BY CourseNum, Grade
ORDER BY CourseNum
I also have the query that gives the total number of grades per CourseNum:
SELECT CourseNum, COUNT(Grade) AS 'Total Count'
FROM TempGrades
GROUP BY CourseNum
I am having trouble figuring out what to do next in combining these 2 queries to get the percentages (i.e., Query1/Query2)
Any help or guidance would be appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi Jody

Something like this?

DROP TABLE #CourseResults

CREATE TABLE #CourseResults (CourseNum VARCHAR(7), Grade CHAR(1))

INSERT INTO #CourseResults (CourseNum, Grade)

SELECT '0500300', 'A' UNION ALL

SELECT '0500300', 'B' UNION ALL

SELECT '0500300', 'A' UNION ALL

SELECT '0500300', 'C' UNION ALL

SELECT '0600000', 'A' UNION ALL

SELECT '0600000', 'C'

SELECT r1.CourseNum, Grade, (100*COUNT(*))/(r2.CourseCount + 0.00)

FROM #CourseResults r1

INNER JOIN (SELECT CourseNum, COUNT(*) AS CourseCount

FROM #CourseResults

GROUP BY CourseNum) r2 ON r2.CourseNum = r1.CourseNum

GROUP BY r1.CourseNum, Grade, r2.CourseCount

ORDER BY r1.CourseNum, Grade

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Garadin One Orange Chip Points: 29613 More actions · Answer 2

Or this one:

[font="Courier New"]DECLARE @Tests TABLE(

CourseNum INT,

Grade CHAR(1))

INSERT INTO @Tests(CourseNum, Grade)

SELECT 0500300, 'A' UNION ALL

SELECT 0500300, 'B' UNION ALL

SELECT 0500300, 'A' UNION ALL

SELECT 0500300, 'C' UNION ALL

SELECT 0600000, 'A' UNION ALL

SELECT 0600000, 'C'

SELECT

T.CourseNum,

T.Grade,

CAST(

ROUND(

( (COUNT(*) / GCount) * 100), 2) AS DECIMAL(9,2)) AS VARCHAR(20)) + '%' Percentage

FROM @Tests T

LEFT JOIN (SELECT CourseNum, CAST(COUNT(*) AS DECIMAL(9,2)) GCount FROM @Tests GROUP BY CourseNum) TCount

ON T.CourseNum = TCount.CourseNum

GROUP BY T.CourseNum, T.Grade, TCount.GCount

ORDER BY T.CourseNum, T.Grade[/font]

Almost the same as Chris', but mine does the extra formatting. That said, the extra formatting shouldn't be done in the database if it can be helped. (Do it on the front end if possible) Also, note what we both did with your sample data. It helps if you can supply sample data like this in the future. A full article on how to easily get sample data in that format is in my signature.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jody Chancey Old Hand Points: 328 More actions · Answer 3

Thank you both very much for your help. Thanks also for the link on how to post to the site. You have to watch out for us noobs :).

Thanks again.

Jody