How to Calculate Percentage With an SQL Statement

1. Overview

Calculating percentages in SQL is a common requirement in various domains, including academic grading, financial reports, and business analytics.

Additionally, SQL offers multiple ways to compute percentages, from basic arithmetic expressions to advanced window functions. However, we need precision, division handling, and query optimization to ensure accurate results.

In this tutorial, we’ll explore different methods for calculating percentages in SQL.

2. Basic Percentage Calculation

We derive percentages by dividing a part by the total and multiplying the result by 100. For example, let’s calculate each student’s contribution to the sum of GPA scores in the Student table of the Baeldung University database:

SELECT 
    name,
    gpa,
    (gpa / (SELECT SUM(gpa) FROM Student)) * 100 AS gpa_percentage
FROM Student;
+-----------------+--------+--------------------+
| name            | gpa    | gpa_percentage     |
|-----------------+--------+--------------------|
| Rita Ora        | 4.2    | 5.148320272564888  |
| Philip Lose     | 3.8    | 4.658004269003868  |
| Samantha Prabhu | 4.9    | 6.006374210119247  |
| Vikas Jain      | 3.3    | 4.045109078288078  |
...
Time: 0.016s

Here, the query first retrieves the SUM(gpa) as the total GPA across all students. Then, it calculates each student’s GPA as a percentage of this total. However, this approach may return overly precise decimal values. We can enhance the readability by rounding the decimals:

SELECT
    name,
    gpa,
    ROUND(((gpa::numeric) / (SELECT SUM(gpa)::numeric FROM Student)) * 100.00, 2) AS gpa_percentage
FROM Student;
+-----------------+--------+----------------+
| name            | gpa    | gpa_percentage |
|-----------------+--------+----------------|
| Rita Ora        | 4.2    | 5.15           |
| Philip Lose     | 3.8    | 4.66           |
| Samantha Prabhu | 4.9    | 6.01           |
| Vikas Jain      | 3.3    | 4.05           |
...
Time: 0.011s

This version rounds up the decimal values of the percentage into two decimal places. Notably, the queries used work for PostgreSQL and MySQL databases. On the other hand, SQL Server doesn’t support the ::numeric casting syntax. Instead we use CAST() or Convert():

SELECT 
    name, 
    gpa, 
    (CAST(gpa AS FLOAT) / (SELECT SUM(CAST(gpa AS FLOAT)) FROM Student)) * 100 AS gpa_percentage
FROM Student;

Additionally, we can also round up to two decimal places:

SELECT 
    name, 
    gpa, 
    ROUND((CAST(gpa AS FLOAT) / (SELECT SUM(CAST(gpa AS FLOAT)) FROM Student)) * 100, 2) AS gpa_percentage
FROM Student;

The results of both queries are the same as the previous ones of PostgreSQL and MySQL databases.

3. Dynamically Calculate Percentage for Grades

In many scenarios, we might need to calculate the distribution of categorical values as percentages. Furthermore, a common use case is analyzing students’ grade distributions within an institution. Instead of computing individual GPA contributions like in the previous section, we now determine how frequently each grade appears as a percentage of the total.

For illustration, let’s create a sample UserGrades table to store student names and their respective grades:

CREATE TABLE UserGrades (
    Name VARCHAR(100),
    Grade VARCHAR(50)
);

INSERT INTO UserGrades VALUES
('John Doe', 'A'),
('Jane Smith', 'B'),
('Alice Brown', 'A'),
('Bob Wilson', 'C'),
('Charlie Davis', 'B'),
('Diana Evans', 'B'),
('Eva Green', 'C'),
('Frank White', 'Pass'),
('Grace Lee', 'None'),
('Henry Moore', 'Fail');
CREATE TABLE
INSERT 0 10
Time: 0.046s

With this table in place, we can calculate the percentage of students in each grade category. To achieve this, let’s count the occurrence of each grade, divide it by the total number of students, and multiply by 100:

SELECT
    Grade,
    ROUND((COUNT(*) * 100.0) / (SELECT COUNT(*) FROM UserGrades), 2) AS Percentage
FROM
    UserGrades
GROUP BY
    Grade
ORDER BY
    Grade;
+-------+------------+
| Grade | Percentage |
|-------+------------|
| A     | 20.00      |
| B     | 30.00      |
| C     | 20.00      |
| Fail  | 10.00      |
| None  | 10.00      |
| Pass  | 10.00      |
+-------+------------+
SELECT 6
Time: 0.013s

Let’s break down the queries used in this illustration:

• COUNT(*): counts the number of students for each grade
• SELECT COUNT(*) FROM UserGrades: retrieves the total number of students
• (COUNT(*) * 100.0) / (SELECT COUNT(*) FROM UserGrades): computes the percentage of students per grade
• ROUND(…, 2): ensures the result is rounded to two decimal places for readability
• GROUP BY Grade: groups results by grade category
• ORDER BY Grade: sorts results alphabetically by grade for a structured presentation

Furthermore, this approach ensures efficient calculations of dynamic percentages for categorical data, making it applicable in academic, financial, and business reporting contexts.

4. Finding Percentages Between Two Columns

Sometimes, we may need to calculate percentages based on two numerical columns rather than aggregating values across multiple rows. In particular, a common example involves determining the percentage of marks obtained by students in an exam.

For illustration, let’s create a Result table and populate it with sample data:

CREATE TABLE Result (
    obtained FLOAT,
    total FLOAT
);

INSERT INTO Result VALUES
    (15, 50),
    (10, 50),
    (20, 50),
    (40, 50),
    (25, 50);
CREATE TABLE
INSERT 0 5
Time: 0.055s

This query creates a new table named Result. We can now compute different percentage calculations.

4.1. Calculating Percentage for Each Row

First, let’s determine the percentages for each row. We divide the obtained marks by the total marks and multiply by 100:

SELECT
    obtained,
    total,
    (obtained / total) * 100 AS percentage
FROM Result;
+----------+-------+------------+
| obtained | total | percentage |
|----------+-------+------------|
| 15.0     | 50.0  | 30.0       |
| 10.0     | 50.0  | 20.0       |
| 20.0     | 50.0  | 40.0       |
| 40.0     | 50.0  | 80.0       |
| 25.0     | 50.0  | 50.0       |
+----------+-------+------------+
SELECT 5
Time: 0.032s

Here, each student’s score is calculated as a percentage of the total marks.

4.2. Calculating Aggregate Percentage

However, we might need to determine the overall percentage across all rows. To achieve this, we sum up the obtained and total columns separately before performing the division:

SELECT
    SUM(obtained) AS total_obtained,
    SUM(total) AS total_marks,
    (SUM(obtained) / SUM(total) * 100) AS overall_percentage
FROM Result;
+----------------+-------------+--------------------+
| total_obtained | total_marks | overall_percentage |
|----------------+-------------+--------------------|
| 110.0          | 250.0       | 44.0               |
+----------------+-------------+--------------------+
SELECT 1
Time: 0.038s

This method ensures we compute a single percentage for the entire dataset instead of evaluating individual rows.

4.3. Excluding a Specific Row

There may be situations where we want to exclude a particular row from the calculation. For example, let’s exclude the row where obtained = 40 and recalculate the aggregate percentage:

SELECT
    SUM(obtained) AS total_obtained,
    SUM(total) AS total_marks,
    (SUM(obtained) / SUM(total) * 100) AS adjusted_percentage
FROM Result
WHERE obtained <> 40;
+----------------+-------------+---------------------+
| total_obtained | total_marks | adjusted_percentage |
|----------------+-------------+---------------------|
| 70.0           | 200.0       | 35.0                |
+----------------+-------------+---------------------+
SELECT 1
Time: 0.041s

In this query, we applied the WHERE clause to exclude a specific row from the percentage computation. This provides flexibility in analysis.

5. Conclusion

In this article, we’ve covered different ways to calculate percentages in SQL, from basic arithmetic to aggregate functions. Furthermore, we explored dynamic grading calculations, column-based percentages, and methods to exclude specific rows. These techniques are useful in academic, financial, and business analysis.