SQL GROUP BY with Aggregate Functions

Any aggregate function from the Aggregate Functions series works inside a GROUP BY query — COUNT, SUM, AVG, MIN, MAX all apply per group, exactly as expected.

What & Why

Nothing new to learn here mechanically — this lesson exists to make explicit what's implicit in every example so far: every aggregate function already covered (COUNT, SUM, AVG, MIN, MAX) works exactly the same way inside a grouped query as it did company-wide. The only difference is that it's now computed once per group instead of once for the entire table.

See How It Works

BUSINESS QUESTION

Marketing wants total and average campaign spend by channel.

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
  channel,
  SUM(spend) AS total_spend,
  ROUND(AVG(spend), 2) AS avg_spend
FROM marketing.campaigns
GROUP BY channel
ORDER BY total_spend DESC, channel;

RESULT — exact output from the displayed Queryflo rows

channel	total_spend	avg_spend
google_ads	115000.00	57500.00
linkedin	50000.00	50000.00
email	45000.00	45000.00

SUM and AVG run independently inside each campaign-channel group.

Now You Try

Practice this concept

Marketing wants total and average campaign spend by channel.

Available schema

marketing

Prefix tables with marketing.table_name.

channeltotal_spendavg_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