Level 6 · Marketing spend per channel

One row per channel with a few totals is the most common shape of an aggregate query. This one runs on marketing_spend, which has one row per day per paid channel.

One group per channel, one value per aggregate

SELECT
  channel,
  COUNT(*) AS spend_days,
  ROUND(SUM(amount), 2) AS total_spend
FROM marketing_spend
GROUP BY channel;

Email has 1,092 days and $100,187.71 in total, social 1,093 days and $345,764.76, and paid 1,091 days and $888,774.13. Every column in the SELECT list is either in the GROUP BY (channel) or inside an aggregate. That is the rule that keeps a grouped query valid.

Let the count check the data

From 2023-01-01 to 2025-12-31 there are 1,096 days. No channel reaches that: paid is missing 5 days, email 4 and social 3, twelve in all. The data notes call these tracking outages. Nobody asked for a data-quality check, but COUNT(*) gave you one for free, and it changes how to read the averages: AVG(amount) is the average over days with data, not over every calendar day. If the missing days really had spend, the true total is a little higher than reported. Mention it in one line when you send the table.

Common mistakes

• Selecting channel without GROUP BY channel. DuckDB reports that the column must appear in the GROUP BY clause or be used in an aggregate function.
• Rounding inside the sum, SUM(ROUND(amount, 2)). Round the final number once.
• Calling the average “per day” without saying it skips missing days.
• Using COUNT(DISTINCT spend_date) and expecting a different answer. There is one row per channel per day, so it gives the same counts here. It only differs when a table can hold several rows for the same day, which is worth checking once on any new table.
• Comparing channels on totals alone. Paid spends almost nine times as much as email in total, and the per-day average shows the same gap, but neither number says which channel earns its budget. That needs revenue or conversions next to the spend.

In SQL Server

This query runs unchanged, because amount is a decimal. One difference worth knowing: AVG of an INT column returns an INT there, so an average of whole numbers loses its decimals. Convert first:

SELECT device, AVG(CAST(pages_viewed AS decimal(10, 2))) AS avg_pages
FROM web_sessions
GROUP BY device;