SQL Mathematical Operators

The final Intermediate-tier series — basic arithmetic and the percentage/growth-rate calculations that show up in nearly every business report.

What & Why

This closing series of the Intermediate tier covers the numeric side of SQL: the five arithmetic operators (+, -, *, /, %), common single-value functions (ABS, ROUND, CEIL, FLOOR, POWER, SQRT, MOD), and the percentage-based calculations — percent-of-total, percent-change, growth rate — that appear in almost every real business report.

See How It Works

BUSINESS QUESTION

Marketing wants each campaign's 10% buffer and unrounded planned spend.

id	name	channel	spend	start_date	end_date	status	target_segment
1	Spring Launch	google_ads	55000.00	2024-01-15	2024-03-31	active	smb
2	Retention Webinar	email	45000.00	2024-02-10	2024-04-15	active	enterprise
3	Finance Retargeting	linkedin	50000.00	2024-03-12	2024-05-31	active	enterprise
4	Enterprise Search	google_ads	60000.00	2024-04-01	2024-06-30	active	enterprise

EXAMPLE QUERY

SELECT
  id AS campaign_id,
  spend,
  spend * 0.10 AS buffer_amount,
  spend + (spend * 0.10) AS planned_spend
FROM marketing.campaigns
ORDER BY planned_spend DESC;

RESULT — exact output from the displayed Queryflo rows

campaign_id	spend	buffer_amount	planned_spend
4	60000.00	6000.0000	66000.0000
1	55000.00	5500.0000	60500.0000
3	50000.00	5000.0000	55000.0000
2	45000.00	4500.0000	49500.0000

Multiplication creates the ten-percent buffer before addition creates planned spend.

Now You Try

Practice this concept

Marketing wants the unrounded ten-percent buffer and planned spend for each campaign.

Available schema

marketing

Prefix tables with marketing.table_name.

campaign_idspendbuffer_amountplanned_spend

marketing.campaigns

Column	Type
id	integer
name	text
channel	text
spend	numeric
start_date	date
end_date	date
status	text
target_segment	text
legacy_id	text

query.sql

Intermediate business practice