Baldwin Apps

Posted on Jul 18

SQL Pattern Series #15: The Percent-of-Total Pattern

#beginners #tutorial #database #sql

Seeing each value's contribution to the whole

SQL Pattern Series #15 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

How to calculate percentages without a self-join
How window functions simplify percent-of-total calculations
Why percentages often communicate better than raw numbers
When to use the Percent-of-Total Pattern

Raw numbers are useful.

But sometimes the more important question is:

How much of the total does this represent?

For example:

What percentage of sales comes from each product category?
What percentage of revenue comes from each region?
What percentage of support tickets belong to each team?
What percentage of traffic comes from each source?

That is where the Percent-of-Total Pattern becomes useful.

The Problem

Imagine a sales table:

Category	Sales
Hardware	5000
Software	3000
Services	2000

The total sales are:

But the raw numbers do not immediately show each category's contribution.

We often want to see:

Category	Percent
Hardware	50%
Software	30%
Services	20%

That requires comparing each value to the overall total.

The Percent-of-Total Pattern

A common solution uses a window aggregate:

SUM(column) OVER ()

This calculates the grand total while still preserving individual rows.

The pattern is:

column /
SUM(column) OVER ()

Each row divides itself by the overall total.

Example

SELECT
    Category,
    Sales,
    Sales * 1.0
        / SUM(Sales) OVER ()
        AS PercentOfTotal
FROM SalesData;

This query calculates the total sales once and makes that value available to every row.

Each row then computes its own percentage.

Example Result

Category	Sales	PercentOfTotal
Hardware	5000	0.50
Software	3000	0.30
Services	2000	0.20

To display percentages:

ROUND(
    Sales * 100.0
    / SUM(Sales) OVER (),
    2
) AS PercentOfTotal

Result:

Category	PercentOfTotal
Hardware	50.00
Software	30.00
Services	20.00

Why This Pattern Matters

Humans are often better at understanding proportions than raw counts.

Consider:

Category A: 5,000
Category B: 3,000
Category C: 2,000

versus:

Category A: 50%
Category B: 30%
Category C: 20%

The percentages immediately communicate relative importance.

Window Functions Make This Easy

Before window functions became widely available, developers often solved this problem using:

SELECT
    Category,
    Sales,
    Sales /
    (
        SELECT SUM(Sales)
        FROM SalesData
    )
FROM SalesData;

That approach works.

But window functions often make the intent clearer because both the row value and the total remain visible in the same query.

Percent of Total Within Groups

Sometimes you want percentages inside groups rather than across the entire table.

For example:

What percentage of regional sales comes from each product?

You can partition the total:

SUM(Sales) OVER (
    PARTITION BY Region
)

Now each region receives its own denominator.

The same pattern applies:

Sales /
SUM(Sales) OVER (
    PARTITION BY Region
)

A Note on Integer Division

One common mistake is:

Sales /
SUM(Sales) OVER ()

If both values are integers, some databases may perform integer division.

To avoid this, force decimal math:

Sales * 1.0

or:

CAST(Sales AS DECIMAL(10,2))

The exact syntax varies by database system.

When I Reach for This Pattern

I typically use the Percent-of-Total Pattern when:

comparing contributions
building dashboards
analyzing revenue distribution
measuring category importance
presenting executive summaries

Examples include:

sales by category
revenue by region
tickets by department
users by platform
expenses by cost center

Key Takeaway

Raw numbers tell you how much.

Percentages tell you how important.

The Percent-of-Total Pattern helps answer:

What share of the whole does this represent?

Sometimes contribution matters more than magnitude.