How to Calculate Percentage in SQL Query: Share of a Total

You have one line on an order or one sale in a region, and you need that line’s share of the total beside it. You calculate percentage in SQL query rows by dividing the part by the whole and multiplying by 100, and no percent operator does that step for you.

The whole can sit on the same row as the part, because SUM() OVER computes the total and repeats it on every line, so the detail stays visible next to its share. Put a decimal into the arithmetic, or an integer divided by an integer total comes back as zero. The sections below cover the row share, the share inside a group, the change from the prior row, and the checks that show the number is the one you meant.

What Does a Share of a Total Mean?

A percentage is a ratio written on a scale of 100. Take the part, divide by the whole, then multiply by 100. One unit out of a larger total is a small ratio, and the percent scale is that same share moved so a report can show it as a familiar number.

SQL will not infer that you wanted a percent. The math is ordinary division and multiplication, and the engine picks the result type from the types of the two sides. Decide whether the column should show the ratio or the percent before you round it, because a later format code may scale the number again.

The other choice is which rows belong in the whole. A share of every row in the result uses one total. A share of one order, one department, or one month uses a total that restarts for each group. A change from the previous row uses the earlier value as the whole, rather than the sum of the group.

How to Calculate Percentage in SQL Query for Each Row

Keep every detail line, attach the group total, then divide. On SQL Server, the OVER clause percentage example puts the line, the order total, and the percent in one select list, and that single pass beats a subquery that recomputes the same total.

1. Name the part. On an order line that is OrderQty. For money it is the amount column.
2. Name the whole with SUM of that same column and OVER. Leave the parentheses empty for one total across the result, or add PARTITION BY the group column so each group has its own total.
3. Divide the part by that sum. Put a decimal into the math before the slash, either with 100.0 times the part divided by the total, or with 1. times the part, which is the form the SQL Server example uses before it scales by 100.
4. Cast or round to the scale you want to show. The example casts to DECIMAL (5, 2), which keeps two digits after the decimal.
5. Read one row back against a hand division before you trust the column.

SELECT SalesOrderID,
 ProductID,
 OrderQty,
 SUM(OrderQty) OVER (PARTITION BY SalesOrderID) AS Total,
 CAST(1. * OrderQty / SUM(OrderQty) OVER (PARTITION BY SalesOrderID) * 100 AS DECIMAL (5, 2)) AS Pct
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43659, 43664);

The literal 1. is a decimal, so the fraction survives the division, and the cast then keeps two decimal places. Four of the published lines look like this.

The first line displays as 3.85 after the cast to two decimal places. A result of zero on a line whose quantity is smaller than the total means the division is still integer. You also cannot attach DISTINCT to that SUM, because the OVER clause does not accept a DISTINCT aggregate.

Once the row share is in hand, the next choice is whether every line should be measured against the full result or only against its own group.

How Does PARTITION BY Change the Share?

PARTITION BY tells the window which rows share a total. With no argument at all, the window is the whole result set after WHERE has already dropped rows. A line removed by WHERE never enters the total, which is right when the report is already limited to two orders, and wrong when you meant the share of the unfiltered table.

Both expressions can sit in one select list. A product line can show its share of the order and its share of every order in the result, because each OVER clause keeps its own window. The group column has to come from FROM. On SQL Server it cannot be an alias invented in that same select list.

A plain aggregate would return one row per group. Window functions keep each row, and the OVER clause decides which of those rows the function can see. With the group chosen, some reports do not want the detail lines at all.

How to Calculate a Percentage on Grouped Rows

GROUP BY is the right tool when the result should be one row per grade, supplier, or region. The percentage is then the group’s count or sum, divided by the total of those groups.

SELECT Grade,
 COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS Pct
FROM MyTable
GROUP BY Grade;

COUNT(*) runs as the grouped aggregate. SUM(COUNT(*)) OVER () then adds those group counts across the grouped result, and the 100.0 keeps the division in decimal math. A subquery that computes the grand total and joins it back returns the same share. The window form does the job in one pass over the grouped rows.

Use the detail form when the reader needs every line. Use this form when the reader needs one row per group and that group’s share. Detail columns and a GROUP BY do not mix in one select list, because the window then sees the grouped rows, not the original lines. Aggregate first, then let the window run on that result, which is the order the engine already uses.

When the part is a subset of the rows, put the condition in the numerator and leave the denominator as the full total. SUM(CASE WHEN status = ‘paid’ THEN amount ELSE 0 END) divided by SUM(amount) is the share of amount that is paid. On PostgreSQL, SUM(amount) FILTER (WHERE status = ‘paid’) is the same numerator.

A change from the prior row is the other percentage this kind of report asks for.

How to Calculate a Percentage Change From the Prior Row

Percentage change uses the earlier value as the whole. LAG pulls that earlier value from the previous row inside a partition, once the window has an ORDER BY that matches the sequence, such as month or order date.

SELECT month_start,
 revenue,
 ROUND(
 100.0 * (revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY month_start))
 / NULLIF(LAG(revenue) OVER (PARTITION BY region ORDER BY month_start), 0), 2) AS pct_change
FROM monthly_revenue;

The first month in each region has no earlier row, so LAG returns null and the change is null. A prior revenue of zero would divide by zero, so NULLIF turns that zero into null and the change comes back null instead of failing the query. Pass a third argument to LAG only when you truly want a stand-in for the missing prior value. Leaving it null is the honest result for a series that has just started.

This pattern needs ORDER BY inside OVER, because a prior row has no meaning without a sequence. The share of a total does not. Adding ORDER BY there changes what SUM means, which is the trap covered with the other breaks below.

Why Does a SQL Percentage Come Out as Zero?

The query can be right and still print 0 for every small share. Integer division truncates the fraction when an integer is divided by an integer: the result is an integer, and the fractional part is cut off rather than rounded.

WHAT INTEGER DIVISION RETURNS

• Divide first: 3 divided by 4 is 0, and (3 / 4) * 100 stays 0 because the fraction was already gone.
• Multiply first: 100 * 3 / 4 equals 75 for these particular integers, and it still drops any share that is not a whole percent.
• Cast too late: CAST(3 / 4 AS decimal(10,2)) is 0.00, because the cast runs after the integer division.
• Decimal first: 3.0 / 4.0 * 100 and 100.0 * 3 / 4 keep the fraction, which is the repair to put in the query.

The SQL Server percentage example multiplies by the decimal literal 1. before it divides, which is that same repair in a short form. On PostgreSQL, multiplying by 100.0 or casting the part to numeric does the same job, and SQLite will also collapse a small share to zero if you multiply by the integer 100 instead of 100.0.

A cast at the end does not rewind an earlier integer division. Cast one side, or multiply by a decimal literal, before the slash. After that, the result can still mislead in a quieter way, because the rounded percents may not add back to 100.

Why Do Rounded SQL Percentages Miss 100?

DECIMAL (5, 2) rounds each line on its own. The exact shares of an order add to 100, and each displayed share is nudged to two decimal places, so the column you see can miss 100 by a small amount. The figures below are the sums of every two-decimal percent the OVER example prints for that order.

Those sums add the published two-decimal values. They are not a second rounding of an exact 100. If a report must tie out to the cent, keep more decimal places in the stored column and round only in the final display, or show the ratio and let the report tool scale it.

A tie-out that lands far from 100 is a different problem. The window is then a running total, the rows in the total are not the rows you think, or the decimal math never started.

What Else Sends a SQL Percentage Off?

Three further breaks show up after the types are right: a zero total, an ORDER BY that shrinks the window, and an alias used before it exists.

How Does NULLIF Handle a Zero Total?

SQL Server stops a query that divides by zero. A null denominator does not raise that error. In the division operator examples, a salesperson with a null sales quota has a null monthly target, because a null divided by a number is null.

A ZERO TOTAL

• The swap: NULLIF(total, 0) returns null when the total equals 0, and returns the total otherwise.
• The result: Dividing by that null yields null, so the query finishes and the empty group stays visible.
• A displayed zero: Wrap the division in COALESCE if the report should show zero instead of a blank.

NULLIF(4, 4) is null and NULLIF(5, 7) returns 5. Equal inputs become null, and unequal inputs keep the first expression. Put NULLIF on the denominator only. Putting it on the part hides real zeros that are valid shares.

What Does ORDER BY Do to a Share Total?

For SUM and the other aggregates that accept a frame, ORDER BY inside OVER without ROWS or RANGE does not mean the report should be sorted. On SQL Server the default frame becomes the rows from the start of the partition through the current row. PostgreSQL’s ordered sum does the same, running from the first row of the partition through the current one, including ties on the sort value.

A share of the total wants the first form. If the percentages shrink as you move down the list, and only the last row sits near 100, the window is a running total. Take the ORDER BY out of that OVER. Put a normal ORDER BY on the query when the only goal is a sorted report.

The Total Alias in the Same SELECT

Repeating the SUM call looks redundant, and the alias is not ready yet. On SQL Server the PARTITION BY expression cannot refer to an alias in the select list, and the percentage expression in that same list cannot see the alias either. Repeat SUM() OVER inside the division, or compute the total in an inner query and divide in the outer query.

SELECT SalesOrderID, ProductID, OrderQty, Total,
 CAST(100.0 * OrderQty / NULLIF(Total, 0) AS DECIMAL (5, 2)) AS Pct
FROM (
 SELECT SalesOrderID, ProductID, OrderQty,
 SUM(OrderQty) OVER (PARTITION BY SalesOrderID) AS Total
 FROM Sales.SalesOrderDetail
 WHERE SalesOrderID IN (43659, 43664)
) AS lines;

The outer query is where a zero total becomes null instead of an error, and where you can read the percent column by name. The inner WHERE is still the filter that decides which rows enter the total.

How Do SQL Dialects Change the Percentage Math?

The arithmetic is the same idea on each engine, and the spelling of the decimal nudge is not. The OVER clause used above is the one documented for SQL Server, Azure SQL, and the Fabric warehouse, so the same percentage shape moves across those products.

MySQL’s manual shows an empty OVER on every row as one partition, and PARTITION BY restarts the sum for each group. Oracle is the dialect in this set with a built-in that returns the share directly. The column name does not change integer division on any of these engines.

Before you copy a query from one engine to another, run 3 divided by 4 on that engine. A zero tells you the decimal nudge still has to be added.

Frequently Asked Questions

Can You Add a Percent Sign to the Result?

Keep the percentage as a number if anything else will sort or sum it, and add the sign only when a person will read that column as text. In SQL Server, FORMAT(0.27, ‘P’) returns 27.00%, because that format code treats the input as a ratio and scales it by 100. Pass a value you already multiplied by 100 and the sign sits on the wrong scale. MySQL can concatenate a percent character onto a number you already scaled, and PostgreSQL can format the number with TO_CHAR in the same spirit.

Why Can’t the Percentage Filter in WHERE?

A window function is computed after WHERE, GROUP BY, and HAVING, so the percentage does not exist yet when the filter runs. Wrap the share query in a subquery or a CTE, then put the comparison on the outer query. The inner WHERE is still the right place to drop rows that should never enter the total, such as cancelled orders.

What Does PERCENT_RANK Return?

PERCENT_RANK returns a rank scaled from 0 to 1, using (rank minus 1) divided by (rows minus 1). It answers where the row sits in an ordered list, and it does not divide quantity or revenue by the group total. A row tied for first comes back as 0, which is not a zero share of the money. Use SUM() OVER when the question is about the share.

What Should You Do When the Total Is Zero or NULL?

A zero total is the case that stops SQL Server, and NULLIF on the denominator turns that stop into a null percentage. A null total divides to null without that error, including when a sales quota is null and the monthly target is null. COALESCE around the whole division replaces either null with a displayed zero, and only if a zero is what the report should claim.

Add the rounded shares for one real group before you send the column out. On the published AdventureWorks lines, order 43659 adds to 100.02 and order 43664 adds to 99.99. If your group does the same, the query is dividing the right whole and the scale is only rounding. If it lands far from 100, the window is a running total or the decimal math never started.