I have a SQL Server table that contains users & their grades. For simplicity's sake, lets just say there are 2 columns - name & grade. So a typical row would be Name: "John Doe", Grade:"A".
I'm looking for one SQL statement that will find the percentages of all possible answers. (A, B, C, etc...) Also, is there a way to do this without defining all possible answers (open text field - users could enter 'pass/fail', 'none', etc...)
The final output I'm looking for is A: 5%, B: 15%, C: 40%, etc...
013 Answers
The most efficient (using over()).
select Grade, count(*) * 100.0 / sum(count(*)) over() from MyTable group by GradeUniversal (any SQL version).
select Grade, count(*) * 100.0 / (select count(*) from MyTable) from MyTable group by Grade;With CTE, the least efficient.
with t(Grade, GradeCount) as ( select Grade, count(*) from MyTable group by Grade ) select Grade, GradeCount * 100.0/(select sum(GradeCount) from t) from t;
I have tested the following and this does work. The answer by gordyii was close but had the multiplication of 100 in the wrong place and had some missing parenthesis.
Select Grade, (Count(Grade)* 100 / (Select Count(*) From MyTable)) as Score
From MyTable
Group By GradeInstead of using a separate CTE to get the total, you can use a window function without the "partition by" clause.
If you are using:
count(*)to get the count for a group, you can use:
sum(count(*)) over ()to get the total count.
For example:
select Grade, 100. * count(*) / sum(count(*)) over ()
from table
group by Grade;It tends to be faster in my experience, but I think it might internally use a temp table in some cases (I've seen "Worktable" when running with "set statistics io on").
EDIT:I'm not sure if my example query is what you are looking for, I was just illustrating how the windowing functions work.
3I simply use this when ever I need to work out a percentage..
ROUND(CAST((Numerator * 100.0 / Denominator) AS FLOAT), 2) AS PercentageNote that 100.0 returns 1 decimal, whereas 100 on it's own will round up the result to the nearest whole number, even with the ROUND(...,2) function!
0You have to calculate the total of grades If it is SQL 2005 you can use CTE
WITH Tot(Total) ( SELECT COUNT(*) FROM table ) SELECT Grade, COUNT(*) / Total * 100
--, CONVERT(VARCHAR, COUNT(*) / Total * 100) + '%' -- With percentage sign
--, CONVERT(VARCHAR, ROUND(COUNT(*) / Total * 100, -2)) + '%' -- With Round FROM table GROUP BY GradeYou need to group on the grade field. This query should give you what your looking for in pretty much any database.
Select Grade, CountofGrade / sum(CountofGrade) *100 from ( Select Grade, Count(*) as CountofGrade From Grades Group By Grade) as sub Group by GradeYou should specify the system you're using.
3The following should work
ID - Key
Grade - A,B,C,D...EDIT: Moved the * 100 and added the 1.0 to ensure that it doesn't do integer division
Select Grade, Count(ID) * 100.0 / ((Select Count(ID) From MyTable) * 1.0)
From MyTable
Group By GradeThis is, I believe, a general solution, though I tested it using IBM Informix Dynamic Server 11.50.FC3. The following query:
SELECT grade, ROUND(100.0 * grade_sum / (SELECT COUNT(*) FROM grades), 2) AS pct_of_grades FROM (SELECT grade, COUNT(*) AS grade_sum FROM grades GROUP BY grade ) ORDER BY grade;gives the following output on the test data shown below the horizontal rule. The ROUND function may be DBMS-specific, but the rest (probably) is not. (Note that I changed 100 to 100.0 to ensure that the calculation occurs using non-integer - DECIMAL, NUMERIC - arithmetic; see the comments, and thanks to Thunder.)
grade pct_of_grades
CHAR(1) DECIMAL(32,2)
A 32.26
B 16.13
C 12.90
D 12.90
E 9.68
F 16.13CREATE TABLE grades
( id VARCHAR(10) NOT NULL, grade CHAR(1) NOT NULL CHECK (grade MATCHES '[ABCDEF]')
);
INSERT INTO grades VALUES('1001', 'A');
INSERT INTO grades VALUES('1002', 'B');
INSERT INTO grades VALUES('1003', 'F');
INSERT INTO grades VALUES('1004', 'C');
INSERT INTO grades VALUES('1005', 'D');
INSERT INTO grades VALUES('1006', 'A');
INSERT INTO grades VALUES('1007', 'F');
INSERT INTO grades VALUES('1008', 'C');
INSERT INTO grades VALUES('1009', 'A');
INSERT INTO grades VALUES('1010', 'E');
INSERT INTO grades VALUES('1001', 'A');
INSERT INTO grades VALUES('1012', 'F');
INSERT INTO grades VALUES('1013', 'D');
INSERT INTO grades VALUES('1014', 'B');
INSERT INTO grades VALUES('1015', 'E');
INSERT INTO grades VALUES('1016', 'A');
INSERT INTO grades VALUES('1017', 'F');
INSERT INTO grades VALUES('1018', 'B');
INSERT INTO grades VALUES('1019', 'C');
INSERT INTO grades VALUES('1020', 'A');
INSERT INTO grades VALUES('1021', 'A');
INSERT INTO grades VALUES('1022', 'E');
INSERT INTO grades VALUES('1023', 'D');
INSERT INTO grades VALUES('1024', 'B');
INSERT INTO grades VALUES('1025', 'A');
INSERT INTO grades VALUES('1026', 'A');
INSERT INTO grades VALUES('1027', 'D');
INSERT INTO grades VALUES('1028', 'B');
INSERT INTO grades VALUES('1029', 'A');
INSERT INTO grades VALUES('1030', 'C');
INSERT INTO grades VALUES('1031', 'F');SELECT Grade, GradeCount / SUM(GradeCount)
FROM (SELECT Grade, COUNT(*) As GradeCount FROM myTable GROUP BY Grade) GradesIn any sql server version you could use a variable for the total of all grades like this:
declare @countOfAll decimal(18, 4)
select @countOfAll = COUNT(*) from Grades
select
Grade, COUNT(*) / @countOfAll * 100
from Grades
group by GradeYou can use a subselect in your from query (untested and not sure which is faster):
SELECT Grade, COUNT(*) / TotalRows
FROM (SELECT Grade, COUNT(*) As TotalRows FROM myTable) Grades
GROUP BY Grade, TotalRowsOr
SELECT Grade, SUM(PartialCount)
FROM (SELECT Grade, 1/COUNT(*) AS PartialCount FROM myTable) Grades
GROUP BY GradeOr
SELECT Grade, GradeCount / SUM(GradeCount)
FROM (SELECT Grade, COUNT(*) As GradeCount FROM myTable GROUP BY Grade) GradesYou can also use a stored procedure (apologies for the Firebird syntax):
SELECT COUNT(*)
FROM myTable
INTO :TotalCount;
FOR SELECT Grade, COUNT(*)
FROM myTable
GROUP BY Grade
INTO :Grade, :GradeCount
DO
BEGIN Percent = :GradeCount / :TotalCount; SUSPEND;
ENDI had a similar issue to this. you should be able to get the correct result multiplying by 1.0 instead of 100.See example Image attached
Select Grade, (Count(Grade)* 1.0 / (Select Count(*) From MyTable)) as Score From MyTable Group By Grade
This one is working well in MS SQL. It transforms varchar to the result of two-decimal-places-limited float.
Select field1, cast(Try_convert(float,(Count(field2)* 100) /
Try_convert(float, (Select Count(*) From table1))) as decimal(10,2)) as new_field_name
From table1
Group By field1, field2;
