How to Get SQL Percentage Calculation as Easy as Pie 

Percentage calculation in SQL can be a bit like traversing an unfamiliar labyrinth. If you’re a beginner, it’s easy to get lost in a maze of numbers and functions. You may stumble over pitfalls that can lead to incorrect results. But fear not because we’re about to make SQL percentage calculations as easy as pie! 

Have you ever wondered how to find the percentage of A’s, B’s, or C’s in a student’s report card? Or you need to determine the percentage of products sold for your sales statistics? These are common tasks in data analysis. So, you see, understanding SQL percentage calculation is important. 

The following are the main points to unravel this calculation: 

• The Basics of SQL Percentage Calculation
• SQL Functions Used for Percentage Calculation
• Finding the Percentage Using Variables
• Percentage Calculation Between Columns
• Using Subqueries in Percentage Calculation
• Using GROUP BY in Percentage Calculation
• Using the OVER Clause
• Common Table Expressions for Percentage Calculation
• Common Mistakes Made When Calculating Percentages in SQL

So, if SQL percentages are a head-scratcher to you, take heart, for we’re here to guide you through this maze. 

Let’s dive in. 

The Basics of SQL Percentage Calculation 

Calculating percentages in SQL Server is like slicing a pie. You need to know the total size (the denominator) and the size of the slice you want (the numerator). To get a percentage, you divide the slice size by the total size and multiply by 100. 

Here’s the SQL math for it: 

Percentage = (Numerator / Denominator) * 100 

Sometimes, you will deal with integer division that will give you wrong percentages. So, in this scenario, the better formula is: 

Percentage = (Numerator * 100.0) / Denominator 

As much as possible, use a DECIMAL data type or an exact numeric data type to get the right results. But if your input numbers are integers, multiply the numerator by 100.0. 

The numerator is what you want as a percentage of the whole, and the denominator is the whole. So, if you want to find the percentage of apple pies out of all the pies sold: 

SELECT (<sum of apple pies sold> * 100.0) / <sum of all pies sold>) AS PercentageSales  
FROM PieSales; 

The numerator is the amount of apple pies sold. At the same time, the denominator is the amount of all pies sold. 

So, that’ll give you the apple pie percentage. Easy as pie! 

The retrieval of the numerator and the denominator need different techniques. And it depends on various factors. 

You’ll get better examples later as we add up to your knowledge. But first, let’s see the common SQL functions used with SQL percentage calculation. 

SQL Functions Used for Percentage Calculation 

Certain SQL aggregate functions will help you get the SQL percentage calculation. Let’s discuss the common ones. 

COUNT 

The COUNT() function is, of course, for counting. When it comes to calculating percentages, you can use it to get a count of a subset of data. Then, you can use it against the count of the whole thing. 

It can be confusing until we get an example. So, here’s a scenario: 

Let’s say students in a class will get a grade of A, B, C, or D. First, you need a count of all students in the class. This is your denominator. Then, you need a count of how many got an A, B, C, or D. And this is your numerator. Finally, to get the percentage of each grade you divide the result of the two. 

So, you will get a result of something like this: 

Grade	Student Count	Percentage
A	2	10
B	7	35
C	8	40
D	3	15
Totals	20	100.0

The total should be 100% representing all students. Later, we’ll give you an SQL query to give the result above.

SUM 

Another nifty function is SUM. Based on its name, you use it to get the total of a numeric column. 

One scenario of using SUM in percentage calculation is in sales. So, this is useful to get the percentage of a product’s sales relative to the total sales of all products. 

First, you need the sum of each product’s sales (the denominator). Then, you need the sum of all product sales (the numerator). Finally, divide the two to get the percentage. The result can be something like this: 

Product	Sales Amount	Percentage
Shoes	450	22.5
Slippers	350	17.5
Socks	700	35.0
Leggings	500	25.0
Total	2000	100.0

AVG 

Another aggregate function used in percentages is the AVG function. You would use it when you want to calculate the average of a set of values. And then, express that average as a percentage of a total or some reference value. Here’s an example scenario: 

Let’s say you have 4 players in a game. They played 5 times and have their average scores. The maximum possible score is 90. So, to get the percentages, you divide their average scores by 90. 

Here’s a possible table: 

Player	Average Score	Percentage: Average Score vs. Max. Possible Score (90)
Toni	85	94.44
Dmitry	86	95.56
Zack	79	87.78
Tor	87	96.67

Getting familiar with these scenarios and functions is one thing. You also need to know the techniques for using them in code. So, let’s dive in on the different techniques to calculate percentages in SQL. 

Finding the Percentage Using Variables 

Variables are like mathematical helpers in SQL. They let you store values for later calculations. 

Imagine you have data on employee salaries. And you want to find the percentage of employees earning above a certain threshold. This is where variables come into play. You can set up two variables. One for the count of employees meeting the criteria. And another for the total employee count. Then, it’s a simple matter of dividing one by the other to get the percentage. 

Let’s start by using fixed values. Here’s the code: 

DECLARE @AboveThresholdCount INT, @TotalEmployeeCount INT 

-- Set the values of variables 
SET @AboveThresholdCount = 7 
SET @TotalEmployeeCount = 290 

-- Calculate the percentage 
DECLARE @Percentage DECIMAL(5,2) 
SET @Percentage = (@AboveThresholdCount * 100.0) / @TotalEmployeeCount 

-- Output the result 
SELECT 'Percentage of employees with a rate above $50: ' + CAST(@Percentage AS VARCHAR) + '%' 

We used 3 variables here. One for the above threshold count (@AboveThresholdCount). Then, another for the total employee count (@TotalEmployeeCount). And finally, for the percentage itself (@Percentage). 

And here’s the result of 2.41%: 

If you try to use the formula for @Percentage = (@AboveThresholdCount / @TotalEmployeeCount) * 100.0, the resulting percentage is 0.0. You can try it yourself. This is the integer division we were talking about earlier. 

Now, let’s use tables from the AdventureWorks database and put the values in SQL variables. Here’s the modified code: 

USE Adventureworks 
GO 
-- Get sample employees and their rates 
SELECT 
 e.BusinessEntityID 
,(SELECT p.Rate FROM HumanResources.EmployeePayHistory p  
  WHERE e.BusinessEntityID = p.BusinessEntityID  
    and p.RateChangeDate = (SELECT MAX(RateChangeDate) FROM HumanResources.EmployeePayHistory WHERE BusinessEntityID = p.BusinessEntityID)) as Rate 
INTO #employee_rates 
FROM HumanResources.Employee e 

DECLARE @AboveThresholdCount INT, @TotalEmployeeCount INT 

-- Set the values of variables 
SELECT @AboveThresholdCount = COUNT(*) FROM #employee_rates WHERE Rate > 50.0 
SELECT @TotalEmployeeCount = COUNT(*) FROM #employee_rates 

-- Calculate the percentage 
DECLARE @Percentage DECIMAL(5,2) 
SET @Percentage = (@AboveThresholdCount * 100.0 / @TotalEmployeeCount) 

-- Output the result 
SELECT 'Percentage of employees with a rate above $50: ' + CAST(@Percentage AS VARCHAR) + '%' 

We borrowed some data from the Employee and the EmployeePayHistory tables. Then, we put them in a temporary table called #employee_rates. Then, we count the employees whose rate is above $50 based on the temp table. And then, we count the number of employees. The rest is the same as the previous code. 

So, the result is also the same as seen below: 

The next section will deal with calculating percentages using table columns. 

Percentage Calculation Between Columns 

Another place where we find inputs to percentage calculation is in database tables. 

Imagine you have a dataset with two columns, Revenue and Expenses. And you want to compute the profit margin, represented as a percentage of revenue. This is where column-to-column percentage calculations come into play. 

Let’s illustrate this with a real-world example in T-SQL code: 

CREATE TABLE FinancialData 
(ProductName VARCHAR(25) NOT NULL, 
 Revenue MONEY NOT NULL, 
 Expenses MONEY NOT NULL) 

 INSERT INTO FinancialData 
 VALUES('Product 1',1500000,35000), ('Product 2',250000,15000), ('Product 3',45000,3400) 

SELECT 
    ProductName, 
    Revenue, 
    Expenses, 
    (Expenses * 100.0 / Revenue) AS ProfitMarginPercentage 
FROM FinancialData; 

And here’s the result: 

Using Subqueries in Percentage Calculation 

A subquery is a SELECT statement within a SELECT statement. Or, sometimes, it’s referred to as a sub-SELECT. 

In this section, we will use subqueries for SQL percentage calculation. And we will use the student grade scenario that we used earlier. 

The following is our list of students and their grades: 

CREATE TABLE student_grades 
(student_name VARCHAR(30) NOT NULL, 
 grade CHAR(1) NOT NULL 
 ) 

 INSERT INTO student_grades 
 VALUES('Leo','A'), ('Vlad','A'), ('Anton','B'),('Dmitry','B'),('Sabrina','B'),('Sam','B'), 
       ('Toni','B'),('Nikolai','B'),('Alex','B'),('Vladimir','C'),('Tatyana','C'), 
       ('Alma','C'),('Mitch','C'),('Justine','C'),('Lori','C'),('Hye-Bin','C'),('Bruce','C'), 
       ('Jun','D'),('Frank','D'),('Manny','D') 

To get the percentages of who got an A, B, C, or D out of this data, here’s the code: 

SELECT 
 a.grade, 
 COUNT(*) as student_count, 
 ((SELECT COUNT(*) FROM student_grades WHERE grade = a.grade) * 100) / (SELECT COUNT(*) FROM student_grades) AS percentage 
FROM student_grades a 
GROUP BY grade; 

We used the COUNT() aggregate function to get the count per grade. And for counting all the students. Then, we used subqueries to get the counts of each. There are other ways to elegantly express this query but you get the point. The numerator is the count of students per grade. But the denominator is the count of all students. 

Check out the result below. Then, compare it to the first table under the COUNT aggregate function topic. 

Let’s expand more on the role of the GROUP BY clause in the next section. 

Using GROUP BY in Percentage Calculation 

The example in the previous section used the COUNT aggregate function. To get the count of students per grade, the data needs to be grouped according to grade. That’s why we used the GROUP BY clause. Without it, you can only get the COUNT of all students. And that’s what we did in the second subquery. 

Let’s have another example using sales data. Then, we’ll use the SUM aggregate function and the GROUP BY clause. 

Here’s the code to make our sample data: 

CREATE TABLE product_sales 
(item VARCHAR(20), sales_amount money) 

INSERT INTO product_sales 
(item, sales_amount) 
VALUES ('Shoes',200.00),('Shoes',250.00), ('Slippers',150.00),('Slippers',200), ('Socks',200.00),('Socks',200.00), 
('Socks',200.00),('Socks',100.00),('Leggings', 250.00),('Leggings', 250.00); 

Then, we will use the same technique to get the sales per product item and the total sales. Here goes: 

SELECT 
 item, 
 SUM(sales_amount) as product_sales, 
 ((SELECT SUM(sales_amount)  
   FROM product_sales  
   WHERE item = a.item) * 100.0) -- numerator (sales per product item) 
   /  
  (SELECT SUM(sales_amount)  
   FROM product_sales) as percentage -- denominator (total sales) 
FROM product_sales a 
GROUP BY item; 

We used two subqueries. One for the product item sales. And the other for the total sales. The result is the same as the table earlier in the SUM function topic. Here it is: 

Let’s explore some other ways to express the query in the next section. 

Using the OVER Clause 

The OVER clause is good for partitioning data, computing running totals, and more. You can use it to get the denominator for percentage calculation. This will help you avoid using a subquery. 

Here is a slight modification of the previous code: 

SELECT 
 item 
 ,SUM(sales_amount) as product_sale 
 ,((SELECT SUM(sales_amount)  
    FROM product_sales  
    WHERE item = a.item) * 100.0) -- numerator (sales per product item) 
   / -- divide 
 (SUM(SUM(sales_amount)) OVER()) as percentage -- denominator (total sales) 
FROM product_sales a 
GROUP BY item; 

Note the changes in the denominator. Instead of using a subquery, it uses an OVER clause to get the same thing. The result set is the same as in the previous section. 

There’s another way to express the same result in a query discussed in the next section. 

Common Table Expressions for Percentage Calculation 

Using Common-Table Expressions (CTE) in SQL percentage calculation is also straightforward. You can prepare the numerator using CTE. Here’s another way to express the same query requirements from the previous section. 

;WITH sales_cte AS 
( 
SELECT 
 item 
,SUM(sales_amount) as total_sales_per_item 
FROM product_sales 
GROUP BY item 
) 
SELECT  
 item 
,total_sales_per_item 
,(total_sales_per_item * 100.0)  -- numerator 
 / -- divide 
 (SELECT SUM(total_sales_per_item) FROM sales_cte) -- denominator 
FROM sales_cte 

The CTE part gets the sales per product item for the numerator. And the total sales (the denominator) comprise the summation of all product item sales. 

Here’s another example of using a CTE and the AVG aggregate function. We will use the scenario that we examined earlier in the AVG function topic. First, let’s create our sample data. 

CREATE TABLE game_scores 
(player VARCHAR(10), score TINYINT) 


INSERT INTO game_scores 
VALUES ('Toni',90),('Toni',80),('Toni',85),('Toni',80),('Toni',90), 
       ('Dmitry',86),('Dmitry',88),('Dmitry',85),('Dmitry',85),('Dmitry',86), 
   ('Zack',80),('Zack',80),('Zack',78),('Zack',81),('Zack',76), 
   ('Tor',90),('Tor',85),('Tor',86),('Tor',85),('Tor',89); 

Then, let’s have the code for getting the average score for each player. And the percentage of the average relative to the maximum possible score of 90. 

;WITH cte_average_scores AS 
( 
SELECT 
 player 
    ,AVG(score) as average_score 
FROM game_scores 
GROUP BY player 
) 
SELECT 
 player 
,average_score 
,ROUND((average_score * 100.0) / 90,2) as average_score_vs_max_possible_score 
FROM cte_average_scores 

Here’s the result set: 

The CTE computes the average score for each player. And we multiply it by 100.0. Finally, we divide it by 90. The final percentage is rounded off to 2 decimal places. 

Given all these examples, you can make mistakes from time to time. The next section will discuss these mistakes and tell you what to do. 

Common Mistakes Made When Calculating Percentages in SQL 

Beginners may stumble into a few common traps when crunching numbers in SQL. This includes percentages. Let’s break down those pitfalls: 

1. Dividing Integers: If you don’t use decimals (like 100.0) in your division, SQL will perform integer division. This can lead to rounding down, giving you incorrect percentages. Always remember to use decimals for precise results. 
2. Handling Zero Counts: If you’re calculating percentages with groups, some groups may have zero counts. When this happens, you might run into division-by-zero errors. Make sure to handle these cases with a CASE statement or NULLIF to avoid errors. 
3. Neglecting Data Quality: Garbage in, garbage out. Incorrect data can lead to wrong percentages. Ensure your data is clean and accurate. 
4. Not Considering Grouping: When calculating percentages for different categories, use the GROUP BY clause. Forgetting this can lead to a single percentage for the entire dataset, not what you want. 
5. Choosing an Incorrect Numerator/Denominator: Depending on your scenario, you might need to choose the right columns for the numerator and denominator. Be clear on what you want to calculate before writing your query. 

Avoid these traps, and you’ll be slicing through percentage calculations in SQL like a pro! 

Takeaways

Let’s wrap-up by distilling the key lessons into bite-sized takeaways: 

• SQL percentage calculations are a skill that grows with practice. So, practice and work with real data. 
• Familiarize yourself with essential SQL functions like COUNT, SUM, and AVG. Knowing when and how to use them is akin to having the right tools for the job. 
• You can use SQL variables and table columns as inputs to calculate SQL percentages. 
• Some ways to get SQL percentage calculation are using subqueries, aggregate functions with GROUP BY, OVER, and CTE. Temporary tables work too. Check out the examples above to get familiar with them. 
• Learn from mistakes. Avoid common pitfalls like integer division, division by zero, and misuse of functions.  

With these points, you’re ready to conquer SQL percentage calculations with confidence. SQL is your gateway to unearthing valuable insights within your data. So, embrace the journey, explore relentlessly, and soon you’ll be a true SQL maestro. 

Typing your queries in SQL Server Management Studio can be a pain sometimes. But no more with the help of SQL Complete. This is like a power tool to improve your productivity in crafting queries at least twice as fast as you do now. It will also help you reduce errors. With its smart code completion, crafting SQL queries is a breeze. That includes SQL percentage calculations. Why not try it out today? 

Tags: sql, sql functions, sql server Last modified: March 14, 2024