Percent of Total With SUM() OVER() in One Query

The report already has every amount, yet another query fetches the total. A window aggregate calculates percent of total alongside the rows in one SQL statement. Define the denominator clearly and guard the division before worrying about formatting.

Decide What Belongs in the Denominator

A percentage answers a relationship between one amount and a specified whole. All sales, filtered sales, and sales within a region are different wholes. Start by naming the intended population. WHERE filters rows before window calculations, so adding a region filter changes an overall denominator unless you calculate the denominator earlier in a separate relational step.

I ask what one hundred percent means before reading the expression. Most confusing percentage reports have correct arithmetic and the wrong population. Which rows should remain in the total when the user hides a category? Decide that contract explicitly. A window function makes the calculation compact, but it cannot decide the business meaning of the denominator.

Create a Small Amount Set

The examples use fictional sales and a decimal amount. Run them in one connection because the table is temporary. Decimal avoids approximate floating arithmetic for stored amounts. Choose precision and scale based on the business range, including the aggregate range. The sample's values are inputs chosen to demonstrate a calculation, not observed sales results.

CREATE TABLE #Sales
(
    SaleID int NOT NULL PRIMARY KEY,
    Region varchar(20) NOT NULL,
    SaleDate date NOT NULL,
    Amount decimal(19,4) NOT NULL
);
INSERT #Sales VALUES
    (1,'North','20260901',120.00),(2,'North','20260902',80.00),
    (3,'South','20260901',60.00),(4,'South','20260903',40.00),
    (5,'West','20260901',0.00);

If negative entries represent refunds, decide whether net share is useful. A negative share and shares above one hundred can be mathematically correct for a net total. They do not resemble ordinary portions of a pie. For a distribution of positive sales, define that positive population separately rather than quietly excluding refunds from a financial reconciliation.

Add an Overall Percent of Total With SUM OVER

An empty OVER clause uses all qualifying rows as the window. SUM returns the same denominator beside each row. Multiply by a decimal literal to avoid integer division if your original amount type is an integer. NULLIF converts a zero denominator into NULL, yielding an undefined percentage instead of a division exception.

SELECT SaleID,Region,Amount,
       SUM(Amount) OVER() AS OverallAmount,
       100.0*Amount/NULLIF(SUM(Amount) OVER(),0) AS SharePct
FROM #Sales
ORDER BY SaleID;

One statement avoids managing a separate total query and its timing. It does not guarantee one physical scan or one pass inside every execution plan. SQL Server can use sorts, spools, and other operators to evaluate windows. Inspect the actual plan and IO if performance is the motivation. Describe the SQL relationship first, then measure the work the engine chooses.

Calculate Percent of Total Within Each Region

PARTITION BY creates a separate denominator for each region. Rows in North divide by the North total, while South uses the South total. A zero total region produces NULL percentages under the same guard. In the sample, West totals zero, so its regional share comes back NULL. Do not automatically replace those NULLs with zero unless the report contract explicitly defines undefined share that way.

SELECT SaleID,Region,Amount,
       SUM(Amount) OVER(PARTITION BY Region) AS RegionalAmount,
       100.0*Amount/NULLIF(SUM(Amount) OVER(PARTITION BY Region),0) AS RegionalSharePct
FROM #Sales
ORDER BY Region,SaleID;

I label the denominator's scope in the output. A column named Share without that context gets copied into another dashboard and starts answering a different question. When the desired result is one row per region, aggregate first. A window over grouped totals then compares those regions with the overall amount without returning every individual sale.

SELECT Region,SUM(Amount) AS RegionalAmount,
       100.0*SUM(Amount)/NULLIF(SUM(SUM(Amount)) OVER(),0) AS OverallSharePct
FROM #Sales
GROUP BY Region
ORDER BY Region;

Give Running Share an Explicit Frame

A running percentage divides a cumulative amount by the full selected total. Use a deterministic order with a unique tie breaker. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW so equal dates do not unexpectedly group as peers under a default RANGE frame. The denominator intentionally has no ORDER BY and remains the complete total.

SELECT SaleID,SaleDate,Amount,
       SUM(Amount) OVER
           (ORDER BY SaleDate,SaleID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
           AS RunningAmount,
       100.0*SUM(Amount) OVER
           (ORDER BY SaleDate,SaleID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
           /NULLIF(SUM(Amount) OVER(),0) AS RunningSharePct
FROM #Sales
ORDER BY SaleDate,SaleID;

With nonnegative amounts, the running share progresses toward the whole. Refunds can make it move backward. A descending amount order answers a contribution ranking question, while chronological order answers accumulation over time. Pick the order that matches the report's purpose. A cumulative chart can look wonderfully smooth while using an order nobody asked for.

Control Filtering and Rounding Separately

If the total must include all regions but the display shows only North, calculate the windows in a CTE first and filter outside. This preserves the full denominator. By contrast, putting the North predicate inside the CTE would make North the whole. Use that distinction deliberately when a dashboard separates calculation scope from visible rows.

WITH shares AS
(
    SELECT SaleID,Region,Amount,
           100.0*Amount/NULLIF(SUM(Amount) OVER(),0) AS SharePct
    FROM #Sales
)
SELECT SaleID,Region,Amount,SharePct,ROUND(SharePct,2) AS DisplaySharePct
FROM shares WHERE Region='North';

Keep extra decimal places for calculations, then round only the display. Rounding each row can make displayed percentages sum slightly above or below one hundred. If a published table must total exactly, use an explicit rounding allocation rule and disclose it. Do not change an arbitrary row until the arithmetic appears pleasant. The small adjustment still represents a business decision.

Check That Percent of Total Adds Up

This check compares unrounded and displayed sums for the same complete population. It also exposes the zero total case. A tolerance accounts for finite decimal precision rather than requiring exact equality after division. Choose a tolerance appropriate to your selected types and report precision. On the sample rows, the raw sum came back as 99.999999999999999, while the rounded display sum was exactly 100. Never reconcile a filtered subset against one hundred when its denominator includes hidden rows.

WITH shares AS
(
    SELECT Amount,SUM(Amount) OVER() AS TotalAmount,
           100.0*Amount/NULLIF(SUM(Amount) OVER(),0) AS SharePct
    FROM #Sales
)
SELECT MAX(TotalAmount) AS TotalAmount,SUM(SharePct) AS RawShareSum,
       SUM(ROUND(SharePct,2)) AS DisplayShareSum,
       CASE WHEN MAX(TotalAmount)=0 THEN N'Undefined denominator'
            WHEN MAX(TotalAmount) IS NULL THEN N'No rows'
            WHEN ABS(SUM(SharePct)-100.0)<=0.0001 THEN N'Within tolerance'
            ELSE N'Investigate population or precision' END AS CheckResult
FROM shares;

Test empty data, all zeros, mixed positive and negative values, and large totals near your type limits. Nullable amounts need their own policy because SUM ignores NULL. A missing amount is different from an amount known to be zero. Preserve that distinction in validation rather than letting an aggregate silently define it for the business.

Publish the Meaning Beside the Number

Use clear names such as OverallSharePct and RegionalSharePct. Record the filter and time range used for the denominator. Save the numeric percentage rather than only a string with a percent sign when another calculation consumes it. Keep currency conversions and aggregation levels consistent before dividing, because mismatched units cannot form a meaningful share.

I reconcile the underlying amounts before the percentages. If totals disagree with the source, a neat distribution only spreads the error neatly. Percent of total becomes reliable when scope, numeric types, zero behavior, and display rounding are explicit. The window expression is short because the important decisions have already been made.

Related reading on this blog: Adding Values WITH OVER and PARTITION BY and SQL SERVER Challenge: Cumulative Total Calculation.

A percentage is not just division, it is a relationship to a defined whole.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.