Pareto Analysis in T-SQL: Which Products Make 80 Percent of Sales

Which products contribute most of the sales total? Pareto analysis adds their cumulative contribution so you can see where the important share ends. The SQL needs a stable order and an explicit threshold rule.

Decide What Sales Means

Choose the reporting period and the amount definition first. Gross sales, net sales after refunds, and invoiced revenue are different inputs. A Pareto calculation cannot repair an ambiguous business definition. Use one consistent definition across the total and every product contribution.

I confirm the amount and period before discussing the eighty-percent line. The familiar eighty/twenty phrase is a pattern to investigate, not a promise about every dataset. The products can reach eighty percent at any share of the catalog. SQL should report that result instead of arranging the data to match the slogan.

Use decimal amounts for this demonstration. Each row represents one sale amount assigned to one product. The temporary table allows repeated ProductID values because the first step will aggregate them. Keep product identity separate from display names so equal names do not accidentally become one product.

CREATE TABLE #ProductSales
(SaleID int PRIMARY KEY, ProductID int NOT NULL,
 SaleAmount decimal(12,2) NOT NULL);
INSERT #ProductSales VALUES
(1,10,400),(2,10,100),(3,20,250),(4,30,150),
(5,40,50),(6,50,50);
SELECT ProductID, SUM(SaleAmount) AS ProductSales
FROM #ProductSales GROUP BY ProductID
ORDER BY ProductSales DESC, ProductID;

Aggregate Before the Pareto Analysis Running Sum

The window calculation belongs at the product grain. Summarize the sale rows first, then order those product totals from largest to smallest. The second ordering column makes tied products deterministic. Without it, the position of equally valuable products can change across executions.

WITH Products AS
(
 SELECT ProductID, SUM(SaleAmount) AS ProductSales
 FROM #ProductSales GROUP BY ProductID
), Running AS
(
 SELECT ProductID, ProductSales,
        SUM(ProductSales) OVER
          (ORDER BY ProductSales DESC, ProductID
           ROWS UNBOUNDED PRECEDING) AS RunningSales,
        SUM(ProductSales) OVER () AS TotalSales
 FROM Products
)
SELECT ProductID, ProductSales, RunningSales, TotalSales,
       CAST(100.0*RunningSales/NULLIF(TotalSales,0)
            AS decimal(9,2)) AS CumulativePercent
FROM Running
ORDER BY ProductSales DESC, ProductID;

The grand total uses an empty OVER clause, so every product receives the same denominator. The running total uses the chosen order and a ROWS frame. Keep the final presentation order aligned with the running order. Otherwise the cumulative figures appear to move backward through the displayed report.

NULLIF protects the division from a zero denominator. It does not declare a zero-sales period meaningful for ABC classification. That case needs an explicit report status or an agreed fallback outside the percentage calculation.

Assign Pareto Analysis Bands With a Clear Boundary

The simplest policy labels each product by its cumulative percentage after inclusion. Products at or below eighty percent are A. Products above eighty through ninety-five are B. The remaining products are C. Those thresholds are sample business rules rather than SQL requirements.

WITH Products AS
(
 SELECT ProductID, SUM(SaleAmount) AS ProductSales
 FROM #ProductSales GROUP BY ProductID
), Running AS
(
 SELECT *, SUM(ProductSales) OVER
  (ORDER BY ProductSales DESC, ProductID ROWS UNBOUNDED PRECEDING)
    AS RunningSales,
  SUM(ProductSales) OVER () AS TotalSales
 FROM Products
)
SELECT ProductID, ProductSales,
       CASE WHEN TotalSales<=0 THEN 'Unclassified'
            WHEN RunningSales<=TotalSales*0.80 THEN 'A'
            WHEN RunningSales<=TotalSales*0.95 THEN 'B'
            ELSE 'C' END AS SalesBand
FROM Running
ORDER BY ProductSales DESC, ProductID;

A product that crosses the eighty-percent threshold belongs to B under this policy. That can leave the A band below eighty percent. If the requirement says include enough products to reach eighty percent, classify using the cumulative amount before the current product instead. The choice changes the bands, not the underlying sales.

Handle the Product That Crosses a Threshold

Subtract the current product from its running total to obtain the prior cumulative amount. This avoids needing a separate LAG operation on the running result. The first product has a prior amount of zero and therefore starts in A for positive totals.

WITH Products AS
(
 SELECT ProductID, SUM(SaleAmount) AS ProductSales
 FROM #ProductSales GROUP BY ProductID
), Running AS
(
 SELECT *, SUM(ProductSales) OVER
 (ORDER BY ProductSales DESC, ProductID ROWS UNBOUNDED PRECEDING)
   AS RunningSales,
 SUM(ProductSales) OVER () AS TotalSales FROM Products
)
SELECT ProductID, ProductSales,
 CASE WHEN TotalSales<=0 THEN 'Unclassified'
      WHEN RunningSales-ProductSales<TotalSales*0.80 THEN 'A'
      WHEN RunningSales-ProductSales<TotalSales*0.95 THEN 'B'
      ELSE 'C' END AS ReachThresholdBand
FROM Running ORDER BY ProductSales DESC, ProductID;

Name the chosen policy in the report. Readers should not need to reverse-engineer a CASE expression to understand why a large product entered a particular band. Neither policy is universally correct. It depends on whether bands describe completed cumulative ranges or the set needed to reach a target.

With the sample rows, product 30 lands in B under the first policy and in A under this one. Products 40 and 50 tie at 50.00 yet land in B and C, which is the tie problem covered next.

Make Tie Behavior Intentional

With ORDER BY but no explicit frame, the default RANGE behavior can include all peers with the same ordering value. If sales alone defines ordering, equal-valued products share a cumulative step. That differs from adding one product at a time with ROWS.

The ProductID tie-breaker provides stable individual positions. It is not a business reason to favor one equally valuable product over another. If tied products must share a band, define a tie-group policy instead. That can make a band overshoot its threshold by the total contribution of the tied group.

I test equal totals directly because ties expose hidden report assumptions. Which matters more for your audience: stable individual ranks or equal treatment of equal sales? Keep the answer consistent across ordering, cumulative sums, and classification.

Review Refunds and Negative Totals

Positive contributions produce a cumulative curve that increases toward the total. Negative product totals can make that curve fall. A product above one hundred percent of net sales is possible when other products have negative totals. That is mathematically valid but changes the usual Pareto interpretation.

Choose whether to analyze positive sales separately and report returns alongside them, or to retain net amounts with a clear explanation. Do not silently discard negative rows to make a smooth chart. Zero-value products also deserve an explicit policy when the catalog contains inactive items.

The thresholds are not a verdict on product quality. A C-band product can support another product, satisfy a contractual need, or have a seasonal role. The database reports concentration of the selected measure. Decisions about stocking and investment require that measure's business context.

Validate the Pareto Analysis Before Reusing It

Check that product totals sum back to the source total for the same period. Verify the last running total equals that denominator when the ordering includes every product. Test a single product, equal totals, a zero period, and a refund-heavy period.

SQL Server 2012 and later support this running-window pattern. Keep period predicates sargable and apply them before aggregation. An index and a suitable reporting table can reduce input work, but do not change the amount definition during tuning. A precise ABC report begins with a precise sales contract, not a colorful label.

Store the report date range with the result so later readers know what the bands describe. A product can move bands when the season or period changes without any data error. Avoid persisting an ABC label as a permanent property unless the business has defined its refresh policy. If the report feeds another process, expose both the numeric contribution and the label. That allows the next reader to verify the chosen boundary instead of trusting an unexplained letter.

Pareto analysis needs a consistent amount definition across products and the grand total. Retain the selected threshold policy so another reader can reproduce the Pareto analysis bands.

Related reading on this blog: SQL SERVER Challenge: Cumulative Total Calculation and How to Find Running Total in SQL Server.

Pareto analysis is not a guaranteed eighty/twenty result, it is a ranked cumulative calculation with explicit business boundaries.

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