How to calculate percentage with a SQL statement

Question

I have a SQL Server table that contains users  amp  their grades  For simplicity s sake  lets just say there are 2 columns - name  amp  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

User · Answer

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

User · Answer

In 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 Grade

User · Answer

The 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 Grade

User · Answer

Instead 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

User · Answer

You 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  TotalRows   Or  SELECT Grade  SUM PartialCount  FROM  SELECT Grade  1 COUNT    AS PartialCount       FROM myTable  Grades GROUP BY Grade   Or  SELECT Grade  GradeCount   SUM GradeCount  FROM  SELECT Grade  COUNT    As GradeCount       FROM myTable       GROUP BY Grade  Grades   You 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  END

User · Answer

You 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 Grade

User · Answer

You 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 Grade   You should specify the system you re using

User · Answer

I 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

User · Answer

I simply use this when ever I need to work out a percentage    ROUND CAST  Numerator   100 0   Denominator  AS FLOAT   2  AS Percentage   Note that 100 0 returns decimals  whereas 100 on it s own will round up the result to the nearest whole number  even with the ROUND   function

User · Answer

SELECT Grade  GradeCount   SUM GradeCount  FROM  SELECT Grade  COUNT    As GradeCount       FROM myTable       GROUP BY Grade  Grades

User · Answer

This 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 13     CREATE 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

User · Answer

The most efficient  using over      select Grade  count      100 0   sum count     over   from MyTable group by Grade  Universal  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

User · Answer

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 Grade

[sql] How to calculate percentage with a SQL statement

Examples related to sql

Examples related to sql-server

Examples related to tsql