In SQL Server, there are various methods to calculate percentages between multiple columns and rows. In this article, we will explore different approaches to calculate SQL percentages using subqueries, the OVER clause, and common table expressions (CTE).

Finding Percentage using Two Variables

Before diving into calculating percentages across rows and columns, let’s start by understanding how to calculate percentages using two basic variables in SQL Server. In the following example, we define three float variables: @num1, @num2, and @perc. We then divide @num2 by @num1 and multiply the result by 100 to calculate the percentage, which is stored in @perc and printed on the console.

DECLARE @num1 as FLOAT
DECLARE @num2 as FLOAT
DECLARE @perc as FLOAT

SET @num1 = 150
SET @num2 = 50
SET @perc = @num2 / @num1 * 100

PRINT @perc

Finding Percentages Between Two Columns

Calculating percentages between two columns is straightforward. You can simply use the division operator “/” to divide values in one column by another. The result will be a list of values representing the division of all the values in the two columns. Let’s consider an example where we have a table called “Result” with two float type columns: “obtained” and “total”. We can find the percentages for each row by dividing the values in the “obtained” column by the values in the “total” column.

CREATE TABLE Result (
  obtained float,
  total float
)

INSERT INTO Result VALUES
  (15, 50),
  (10, 50),
  (20, 50),
  (40, 50),
  (25, 50)

SELECT obtained, total, obtained / total * 100 as 'percentage' FROM Result

Finding Percentages via Subqueries

Calculating SQL percentages between two columns is straightforward. However, finding percentages across rows for different scenarios can be more complex. Let’s start with a simple scenario where we need to find the percentage of a value in a column among all the rows in that column. In this case, we can use subqueries. The outer query will multiply all the values in the column by 100 and divide the result by the sum of all the values in the column. Here’s an example:

CREATE TABLE Scores (
  val float
)

INSERT INTO Scores (val) VALUES
  (15),
  (10),
  (20),
  (40),
  (25)

SELECT val, val * 100 / (SELECT SUM(val) FROM Scores) as 'Percentage of Total' FROM Scores

If you want to exclude specific values while calculating the percentage of the total, you can use the WHERE clause. For example, the following script excludes the value 40:

SELECT val, val * 100 / (SELECT SUM(val) FROM Scores WHERE val < 40) as 'Percentage of Total' FROM Scores WHERE val < 40

You can also round off the percentage values using the “round” function. The following script rounds off the percentage values to 2 decimal places:

SELECT val, ROUND(val * 100 / (SELECT SUM(val) FROM Scores WHERE val < 40), 2) as 'Percentage of Total' FROM Scores WHERE val < 40

Real-World Example

Let’s consider a real-world example using the Northwind sample database. Suppose we want to find the percentage of products supplied by each supplier. To calculate these percentages, we need two values: the total number of all products (which we can get using the COUNT function) and the total number of products supplied by each supplier (which we can get using the GROUP BY function). We can then multiply the count of products grouped by supplier IDs by 100 and divide the result by the total count of products. Here’s how we can use subqueries to find these SQL percentages:

USE Northwind

SELECT SupplierID, COUNT(*) * 100.0 / (SELECT COUNT(*) FROM Products) as 'Supplier Percentage' FROM Products GROUP BY SupplierID

Using the OVER Clause

The OVER clause is a powerful window function that allows us to calculate values over a range of values. We can also use the OVER clause to calculate SQL percentages. This eliminates the need for subqueries. Let’s see an example:

USE Northwind

SELECT SupplierID, COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () as 'Supplier Percentage' FROM Products GROUP BY SupplierID

The output will be similar to the results obtained using subqueries.

Using Common Table Expressions (CTE)

Common table expressions (CTE) can also be used to calculate percentages. Let’s start by understanding how to use CTEs to find percentages between values in two columns. The following script calculates the percentages by dividing the values in the “obtained” column by the values in the “total” column:

WITH ResultCTE (Obtained, Total, Percentage) AS (
  SELECT obtained, total, (obtained / total) * 100 Percentage FROM Result
)
SELECT * FROM ResultCTE

We can also use CTE expressions to calculate more complex SQL percentages, similar to what we did with the OVER clause. For example, the following script calculates the percentage of products supplied by each supplier:

USE Northwind;

WITH ProductCTE (SupplierID, Supplier_Count) AS (
  SELECT SupplierID, COUNT(*) FROM Products GROUP BY SupplierID
)
SELECT SupplierID, Supplier_Count * 100.0 / (SELECT SUM(Supplier_Count) FROM ProductCTE) as 'Percentage Supplies' FROM ProductCTE;

Finally, we can combine the OVER clause with CTE to calculate percentages. For example, the following script calculates the percentage of products supplied by each supplier, along with the percentage of unit prices paid to all suppliers for all the products:

USE Northwind;

WITH ProductCTE (SupplierID, Supplier_Count, Price_Percentage) AS (
  SELECT SupplierID, SUM(UnitPrice) as 'Total Price Product', (SUM(UnitPrice) * 100) / SUM(SUM(UnitPrice)) OVER () as 'Percentage of Total Price' FROM Products GROUP BY SupplierID
)
SELECT * FROM ProductCTE;

Conclusion

In this article, we explored different methods to calculate SQL percentages between multiple columns and rows. We learned how to use subqueries, the OVER clause, and common table expressions (CTE) to find SQL percentages. These techniques provide flexibility and efficiency in calculating percentages in SQL Server.