To calculate the percentage in an Sql statement, we can use the basic percentage arithmetic formula along with some basic SQL Server functions. In this article, we will see how to calculate percentage in SQL statement using an illustration of a student’s marks.
Let us consider a table with a student’s mark list. It will have the maximum marks for the subject and the mark obtained by the student. You want to find the percentage of total marks obtained by the student.
Here is the sample table with data:
Percentage Formula
To start with let us see the mathemetical formula for percentage calculation and then see how to implement it in an SQL statement.
% Percentage = (Actual Value / Total Value) × 100
(or) % Percentage = (Actual Value x 100 ) / Total Value
In our case it is:
% of total marks obtained = (Total Marks Obtained * 100) / Total Maximum Marks
In SQL Statement, we can use this formula as it is along with the SUM() function to get the Total Marks Obtained and Total Maximum Marks
SQL Statement
SELECT Student_ID,
SUM(Marks_Obtained) * 100 / SUM(Full_Marks) AS 'Percentage'
FROM MTB_Student_Marks
GROUP BY Student_ID
Here is the Result:
SQL statement to get the percentage in decimal
The above statement returns the percentage value in integer. The decimals are rounded to the nearest integer. If you want the result in decimal, then add a decimal to 100 like 100.0 in the formula. Here is the sample SQL statement:
SELECT Student_ID,
SUM(Marks_Obtained) * 100.0 / SUM(Full_Marks) AS 'Percentage'
FROM MTB_Student_Marks
GROUP BY Student_ID
Here is the result:
Reference
- Read more about arithmetic operations in SQL Server at Microsoft Docs.