Percent of total with window functions in SQL

Introduction

We use percent of total to see how much each part contributes to the whole in a simple way.

When you want to know what percent each product's sales is of total sales.

When you want to compare each student's score as a percent of the total class score.

When you want to find out what percent each department's expenses are of the total company expenses.

Syntax

SQL

SELECT column1,
       column2,
       value_column,
       100.0 * value_column / SUM(value_column) OVER () AS percent_of_total
FROM table_name;

The SUM() OVER () calculates the total sum of the value_column for all rows.

Multiplying by 100.0 converts the fraction to a percentage.

Examples

Shows each product's sales and what percent it is of total sales.

SQL

SELECT product_name,
       sales,
       100.0 * sales / SUM(sales) OVER () AS percent_of_total
FROM sales_data;

Calculates each department's expense as a percent of total expenses.

SQL

SELECT department,
       expense,
       100.0 * expense / SUM(expense) OVER () AS percent_of_total
FROM expenses;

Sample Program

This query creates a table with product sales, inserts data, and then shows each product's sales and its percent of total sales rounded to 2 decimals.

SQL

CREATE TABLE sales_data (
    product_name VARCHAR(20),
    sales INT
);

INSERT INTO sales_data VALUES
('Apples', 50),
('Bananas', 30),
('Cherries', 20);

SELECT product_name,
       sales,
       ROUND(100.0 * sales / SUM(sales) OVER (), 2) AS percent_of_total
FROM sales_data
ORDER BY product_name;

Important Notes

Window functions like SUM() OVER () do not reduce the number of rows returned.

You can use ROUND() to make the percent easier to read.

Make sure to multiply by 100.0 (not 100) to get a decimal percent.

Summary

Percent of total helps compare parts to the whole easily.

Use SUM() OVER () to get the total sum without grouping rows.

Multiply by 100.0 to convert fraction to percent.