How to Get a Rate in SQL: 3 Methods Analysts Trust (2026)

Last updated: Q2 2026.

You write what looks like a correct query, run it, and the rate comes back as 0. Or worse, the whole thing fails with a divide-by-zero error the moment one segment has no data. Almost every analyst who reports conversion, click-through, or churn numbers out of a database has hit both bugs, usually with someone standing there waiting for the figure.

Knowing how to get a rate in SQL comes down to one idea and two safeguards. A rate is a numerator divided by a denominator, multiplied by 100. The safeguards stop integer division from rounding your answer down to zero, and stop an empty denominator from throwing an error. Get those right and every marketing rate you track falls out of three reliable query patterns.

This guide covers the three methods analysts use to calculate a rate, the exact SQL for each, the two gotchas that quietly break rate queries, and how the syntax shifts across SQL Server, PostgreSQL, and MySQL. Every example uses a real marketing rate: lead conversion, email click-through, pipeline share, and win rate.

What “Getting a Rate” Means in SQL

To get a rate in SQL, divide a numerator by a denominator and multiply by 100, while guarding against integer division and division by zero. That one shape covers conversion rate, click-through rate, open rate, churn rate, and win rate. Only the two columns you count actually change.

Every marketing rate is the same question asked of different data: how many of the things that could have happened actually did? A conversion rate counts converted leads over total leads. An open rate counts opens over emails delivered. A win rate counts won deals over closed deals. Once you read a rate as a count over a count, the SQL stops being about the metric and starts being about two numbers and one safe division.

Every marketing rate, from conversion to CTR to churn, has the same shape: a count of the thing that happened over a count of the thing that could have happened.

Rate vs Ratio vs Percentage

These three get used interchangeably, and for most reporting the gap is small. A ratio compares two quantities directly (3:1). A rate is one quantity per unit of another, often over time (5% churn per month). A percentage is a rate expressed per hundred. In SQL you compute all three the same way, and multiplying by 100 is the step that turns a raw fraction into the percentage a dashboard displays.

Marketing Rates as Numerator ÷ Denominator

Before writing a query, name the two columns. Most reporting rates map cleanly:

• Conversion rate: converted leads ÷ total leads
• Click-through rate (CTR): clicks ÷ emails delivered (or impressions, for ads)
• Open rate: opens ÷ emails delivered
• Win rate: won deals ÷ closed deals
• Churn rate: customers lost ÷ customers at start of period

Deciding which lead and conversion rates are worth tracking is a strategy question; once you have them, the calculation that follows is mechanical.

The Three Methods to Calculate a Rate in SQL

SQL offers three reliable patterns for a rate: conditional aggregation, window functions, and a joined ratio, each suited to a different question. The table below shows when to reach for which, and the rest of this section works through the SQL for all three.

Method 1: Conditional Aggregation (Success and Failure Rates)

Conditional aggregation calculates a rate by averaging a CASE expression that returns 1 for a hit and 0 for a miss. It is the cleanest pattern when every row is a pass-or-fail event, which is most funnel rates. Swap the 1-or-0 for a per-band point value and the same CASE becomes a first-match scoring rule that returns each band’s points, where the first matching revenue band sets the score and the branches below it never run.

-- Lead conversion rate: share of leads that became customers
SELECT
    COUNT(*)                                               AS total_leads,
    SUM(CASE WHEN converted = 1 THEN 1 ELSE 0 END)         AS converted_leads,
    AVG(CASE WHEN converted = 1 THEN 1.0 ELSE 0 END) * 100 AS conversion_rate_pct
FROM leads;

The trick is the 1.0 inside the CASE. It makes the average a decimal, so 4,700 conversions out of 10,000 leads returns 47.0, not 0. Swap in a plain 1 and SQL Server and PostgreSQL average integers and truncate the answer to 0. The same pattern gives you an MQL-to-SQL conversion rate once you settle what actually counts as a sales-qualified lead, because that definition is exactly the condition inside the CASE.

PostgreSQL offers a tidier form with the FILTER clause: COUNT(*) FILTER (WHERE converted) * 100.0 / COUNT(*).

Method 2: Window Functions (Share of a Total)

A window function calculates each row’s share of a total by dividing a per-group sum by the grand total computed with OVER(). Reach for it when the rate is “what percentage of the whole does this segment represent?”

-- Each lead source's share of total pipeline value
SELECT
    lead_source,
    SUM(deal_value)                                        AS source_value,
    SUM(deal_value) * 100.0 / SUM(SUM(deal_value)) OVER () AS pct_of_total
FROM opportunities
GROUP BY lead_source
ORDER BY pct_of_total DESC;

The nested SUM(SUM(deal_value)) OVER () reads oddly the first time. The inner SUM totals each source; the outer windowed SUM adds those group totals into one grand total that every row divides against. Change OVER () to OVER (PARTITION BY region) and each source’s share is calculated within its region instead of across the whole table.

Method 3: Joined or CTE Ratio (Two Independent Metrics)

A CTE-based ratio calculates a rate from two metrics that live in separate aggregates, totaling each one first and then dividing. Click-through rate is the classic case: clicks and deliveries are counted independently, then divided.

-- Email click-through rate per campaign (clicks / emails delivered)
WITH campaign_stats AS (
    SELECT
        campaign_id,
        SUM(delivered) AS emails_delivered,
        SUM(clicks)    AS total_clicks
    FROM email_events
    GROUP BY campaign_id
)
SELECT
    campaign_id,
    total_clicks * 100.0 / NULLIF(emails_delivered, 0) AS click_through_rate_pct
FROM campaign_stats;

The CTE keeps the query legible: each total is named before the division happens. NULLIF(emails_delivered, 0) returns NULL instead of an error for a campaign that never sent, and the 100.0 keeps the result a decimal. The accuracy of any CTR depends on where the click and impression data is captured in the first place; the SQL only formats what your tracking already recorded.

The Two Gotchas That Break SQL Rate Queries

The two errors that break SQL rate calculations are integer division, which silently returns zero, and division by zero, which throws an error and stops the query. Both are easy to prevent once you know they exist, and between them they explain almost every rate that “should work” but returns 0 or fails.

Integer Division Truncates to Zero

In SQL Server and PostgreSQL, dividing an integer by an integer returns an integer, so the decimal part is thrown away. 47 / 100 is not 0.47; it is 0. Force one side to a decimal and the result comes back correct.

-- WRONG: integer / integer truncates to 0 in SQL Server and PostgreSQL
SELECT 47 / 100 AS broken_rate;                 -- returns 0

-- RIGHT (A): multiply by 1.0 to force a decimal
SELECT 47 * 1.0 / 100 AS rate;                  -- returns 0.47

-- RIGHT (B): cast the numerator to decimal
SELECT CAST(47 AS DECIMAL(10,4)) / 100 AS rate; -- 0.4700

-- PostgreSQL shorthand cast
SELECT 47::numeric / 100 AS rate;               -- 0.47

Division by Zero Throws an Error

The moment a denominator is zero, SQL Server and PostgreSQL halt the query with a divide-by-zero error. A single segment with no leads or no impressions is enough to take down the whole report. Wrap the denominator in NULLIF so a zero becomes NULL and the row returns NULL instead of crashing.

-- Option A: NULLIF (short; returns NULL for empty cohorts)
SELECT won_deals * 100.0 / NULLIF(total_deals, 0) AS win_rate_pct
FROM sales_by_rep;

-- Option B: CASE (returns 0 instead of NULL, if you prefer a number)
SELECT
    CASE WHEN total_deals = 0 THEN 0
         ELSE won_deals * 100.0 / total_deals
    END AS win_rate_pct
FROM sales_by_rep;

Calculating Rates by Group with GROUP BY

To calculate a rate for each category in SQL, add that category to GROUP BY and apply conditional aggregation within each group. This is how one blended conversion rate becomes a rate per channel, per campaign, or per rep, which is where the number starts to be useful.

-- Conversion rate by lead source, rounded and divide-by-zero safe
SELECT
    lead_source,
    COUNT(*)                                        AS leads,
    SUM(CASE WHEN converted = 1 THEN 1 ELSE 0 END)  AS conversions,
    ROUND(
        SUM(CASE WHEN converted = 1 THEN 1.0 ELSE 0 END)
        * 100.0 / NULLIF(COUNT(*), 0)
    , 2)                                            AS conversion_rate_pct
FROM leads
GROUP BY lead_source
ORDER BY conversion_rate_pct DESC;

This query folds all three safeguards into one statement: the 1.0 for decimal output, NULLIF against an empty source, and ROUND(..., 2) for a clean two-decimal figure. The same grouped pattern handles win rate broken out by account tier, where each tier becomes a row and the CASE condition tests for a closed-won stage.

Filtering Groups by Rate with HAVING

To keep only the groups whose rate clears a threshold, filter on the aggregate with HAVING, not WHERE. WHERE filters rows before grouping; HAVING filters the computed rate after.

-- Lead sources converting below 2%
SELECT lead_source
FROM leads
GROUP BY lead_source
HAVING AVG(CASE WHEN converted = 1 THEN 1.0 ELSE 0 END) < 0.02;

Rounding a Rate to Two Decimal Places

To round a rate in SQL, wrap the calculation in ROUND(value, 2); in PostgreSQL, cast a floating-point value to numeric first, or the function call fails. A raw rate like 47.638291 is just noise on a dashboard, so most reports round to one or two decimals.

-- SQL Server and MySQL: ROUND works directly
SELECT ROUND(won_deals * 100.0 / NULLIF(total_deals, 0), 2) AS win_rate_pct
FROM sales_summary;

-- PostgreSQL: round(double precision, int) does not exist; cast to numeric
SELECT ROUND((won_deals * 100.0 / NULLIF(total_deals, 0))::numeric, 2) AS win_rate_pct
FROM sales_summary;

The PostgreSQL note trips up people moving over from SQL Server. Postgres only defines ROUND(numeric, integer), not ROUND(double precision, integer), so a rate that became a float (for example from AVG() on a float column) needs an explicit ::numeric cast, as the PostgreSQL math functions reference spells out. Numeric literals like 100.0 usually keep the expression numeric and sidestep the issue, while SQL Server’s ROUND takes a float without complaint. The same two-decimal formatting applies when you report the compounding math behind a monthly churn rate, where rounding too early distorts the annualized figure.

SQL Dialect Differences: SQL Server, PostgreSQL, MySQL

Rate syntax is mostly portable across databases, but integer division, the ROUND signature, and the FILTER clause differ between SQL Server, PostgreSQL, and MySQL. The table below is the quick reference for moving a rate query from one engine to another.

PostgreSQL’s FILTER clause is worth adopting if you live in Postgres: COUNT(*) FILTER (WHERE converted) * 100.0 / COUNT(*) reads more plainly than the equivalent CASE and runs just as fast. NULLIF, by contrast, behaves identically everywhere, so the divide-by-zero guard is the one habit you never have to rewrite between engines.

Frequently Asked Questions

Next Steps

Pick the method that matches your question before you write a line: conditional aggregation for a pass-or-fail rate, a window function for a share of the whole, a CTE ratio for two metrics counted separately. Most rate bugs come from forcing the wrong pattern onto the data.

Then make the two safeguards automatic. Multiply by 1.0 and wrap every denominator in NULLIF by default, even when this quarter’s data has no zeros, because next quarter’s will. A rate query that has been correct for months still breaks the first time a new segment ships with no events.

If you calculate the same handful of rates every week, save the grouped query as a view so the safeguards live in one place. From there, which rates earn a spot on a leadership dashboard is a reporting decision rather than a SQL one, and the dashboard metrics that matter for SaaS guide covers the ones worth the space.