SQL Percentage Calculations

Part divided by whole, times 100 — the reusable formula behind open rate, click rate, conversion rate, and percent-of-total questions.

What & Why

Every "what percentage" question reduces to the same formula: (part / whole) * 100. The genuinely easy way to get this wrong is forgetting to force decimal division — dividing two integers first (as the earlier Division lesson covered) truncates before the percentage math even happens.

See How It Works

BUSINESS QUESTION

Marketing wants open and click rates for every email send.

id	campaign_id	sent_at	recipients	opens	clicks	unsubscribes	bounces
201	1	2024-01-20 15:00:00+00	1200	540	180	9	24
202	2	2024-02-15 16:30:00+00	800	420	96	5	12
203	3	2024-03-18 13:00:00+00	1500	610	225	14	31
204	4	2024-04-08 14:00:00+00	0	0	0	0	0

Watch a safe percentage calculationExample 1 of 4

clicks::numeric / NULLIF(recipients, 0) * 100

id	recipients	opens	clicks	open_rate_pct	click_rate_pct
201	1200	540	180	45.00%	15.00%
202	800	420	96	?	?
203	1500	610	225	?	?
204	0	0	0	?	?

Both rates use Send 201's real recipient count.

EXAMPLE QUERY

SELECT
  id AS send_id,
  ROUND(opens::numeric / NULLIF(recipients, 0) * 100, 2) AS open_rate_pct,
  ROUND(clicks::numeric / NULLIF(recipients, 0) * 100, 2) AS click_rate_pct
FROM marketing.email_sends
ORDER BY send_id;

Now You Try

Practice this concept

Marketing wants safe open and click percentages for every email send.

Available schema

marketing

Prefix tables with marketing.table_name.

send_idopen_rate_pctclick_rate_pct

marketing.email_sends

Column	Type
id	integer
campaign_id	integer
sent_at	timestamp with time zone
recipients	integer
opens	integer
clicks	integer
unsubscribes	integer
bounces	integer
legacy_send_group	text

query.sql

Intermediate business practice