SQL Division

The / operator divides one value by another — but integer-divided-by-integer truncates the decimal in Postgres, a genuinely common surprise.

What & Why

Plain / divides. The behavior depends on the types involved: dividing two integer values performs integer division, truncating any decimal remainder. Dividing when at least one side is a decimal type gives a proper fractional result.

See How It Works

BUSINESS QUESTION

Marketing wants click-through rate 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

EXAMPLE QUERY

SELECT
  id AS send_id,
  ROUND(clicks * 1.0 / NULLIF(recipients, 0), 4) AS click_through_rate
FROM marketing.email_sends
ORDER BY send_id;

RESULT — exact output from the displayed Queryflo rows

send_id	click_through_rate
201	0.1500
202	0.1200
203	0.1500
204	NULL

The three non-zero sends return their exact click-through rates.

Now You Try

Practice this concept

Marketing wants click-through rate for every email send.

Available schema

marketing

Prefix tables with marketing.table_name.

send_idclick_through_rate

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