Ratios, percentages and rates

Turn a business question into a formula, and dodge the traps behind most wrong report numbers.

Lesson 10 covered the mechanics: operators, integer division, NULLIF and rounding. Lesson 17 covered CASE. This lesson is about the part that actually trips people up: deciding what to divide by what, and whether the math should run per row or per group.

Step 1: say it in English first

Before writing any SQL, finish this sentence: ”___ out of ___.”

Most wrong rates have the right top number and the wrong bottom one. Win rate divides by closed deals, not all deals, because open deals haven’t won or lost yet. Including them makes every rep look worse.

Step 2: count the top number with CASE

The bottom number is usually a plain COUNT(*). The top number is a subset, so it’s the conditional count from lesson 17:

SELECT COUNT(*)                                     AS closed_deals,
       SUM(CASE WHEN is_won = 1 THEN 1 ELSE 0 END)  AS won_deals
FROM opportunities
WHERE closed_at IS NOT NULL;

That returns 47 closed deals and 23 won. Remember the trap from lesson 17: COUNT(CASE WHEN is_won = 1 THEN 1 ELSE 0 END) returns 47, not 23, because it counts the 0s too (Counting with CASE: SUM vs. COUNT).

Step 3: divide, with 100.0 first

SELECT ROUND(100.0 * SUM(CASE WHEN is_won = 1 THEN 1 ELSE 0 END)
             / COUNT(*), 1) AS win_rate_pct
FROM opportunities
WHERE closed_at IS NOT NULL;

Result: 48.9. Three habits are packed into that one line:

If the bottom number could ever be 0, guard it: / NULLIF(COUNT(*), 0) returns NULL instead of an error (Divide-by-zero protection).

The big one: ratio of totals, not average of ratios

This trap produces numbers that look right and aren’t. Here’s the win rate for each lead source:

Now someone asks for the overall win rate. Averaging that last column gives 52.5%. The real answer is 48.9% (23 won out of 47 closed).

The average is wrong because it gives Inbound’s 4 deals the same weight as Webinar’s 12. Whenever you combine rates, go back to the totals and divide those:

-- Right: total won / total closed
SUM(won_deals) * 100.0 / SUM(closed_deals)
 
-- Wrong: every group counts equally, whatever its size
AVG(win_rate_pct)

The same goes for averaging averages, such as average deal size per rep. Field guide: Weighted average vs. average of averages.

Percent of total

Each lead source’s share of the open pipeline. The bottom number is the total across all groups, so it gets its own small query in parentheses:

SELECT lead_source,
       SUM(amount) AS pipeline,
       ROUND(100.0 * SUM(amount)
             / (SELECT SUM(amount) FROM opportunities WHERE closed_at IS NULL),
             1) AS pct_of_pipeline
FROM opportunities
WHERE closed_at IS NULL
GROUP BY lead_source
ORDER BY pct_of_pipeline DESC;

The query in parentheses runs once and returns one number, $1,032,500. Two things to check:

• Same filter inside and out. If the inner query left out WHERE closed_at IS NULL, you’d be dividing open pipeline by every deal ever, and the shares would be far too small.
• The shares should add up to about 100. Here they total 100.2, because each one was rounded. Anything far off means the two filters don’t match.

The field guide’s Percentages and share of total card shows the window-function version, SUM(SUM(amount)) OVER (), which you’ll meet in a later lesson.

Percent change

The formula is (new − old) / old. You always divide by the old value.

SELECT SUM(CASE WHEN closed_at >= '2025-01-01' AND closed_at < '2026-01-01'
                THEN amount END) AS won_2025,
       SUM(CASE WHEN closed_at >= '2026-01-01' AND closed_at < '2027-01-01'
                THEN amount END) AS won_2026
FROM opportunities
WHERE is_won = 1;

That returns $270,000 for 2025 and $650,500 for 2026 so far. The change:

SELECT ROUND(100.0 * (650500 - 270000) / NULLIF(270000, 0), 1) AS pct_change;   -- 140.9

You can’t reuse won_2025 and won_2026 in the same SELECT that names them, so in a real report you’d either repeat the two SUM(CASE ...) expressions or calculate them first in a CTE (an intermediate lesson).

Two ways percent change misleads:

Weighted totals

To weight values, multiply on each row first, then add up. Weighted pipeline multiplies each open deal by its chance of closing. The sample data has no probability column, so the short form of CASE from lesson 17 assigns one per stage:

SELECT stage,
       SUM(amount) AS pipeline,
       SUM(amount * CASE stage
                      WHEN 'Prospecting'   THEN 0.10
                      WHEN 'Qualification' THEN 0.20
                      WHEN 'Proposal'      THEN 0.50
                      WHEN 'Negotiation'   THEN 0.75
                    END) AS weighted_pipeline
FROM opportunities
WHERE closed_at IS NULL
GROUP BY stage;

Negotiation has $334,000 in pipeline, weighted to $250,500. The order matters: SUM(amount * weight) multiplies each deal by its own weight. SUM(amount) * AVG(weight) blends them and gives a different number whenever big deals and small deals sit at different weights.

Cheat sheet

When a number looks wrong, check the field guide’s Math traps at a glance table. It lists each symptom, its cause and the fix.

Try it

What’s the win rate for each lead source? Count only closed deals, and show the lead source, closed deals, won deals and the win rate as a percent rounded to 1 decimal place, best first. Deals with no lead source form their own group, and that’s fine.

Your result should match the table above. Field guide exercise M1 is the same question for all deals at once.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson