Grouping into a Chart-Ready Table

One row per bar

A chart is a table in disguise. A bar chart of revenue by source needs exactly one row per source and one column per value. Getting from thousands of order rows to a handful of summary rows is the job of GROUP BY.

SELECT source,
       COUNT(*) AS orders,
       ROUND(SUM(net_amount), 2) AS revenue
FROM orders
WHERE status = 'delivered'
  AND order_date >= '2026-01-01'
  AND order_date < '2026-04-01'
GROUP BY source
ORDER BY revenue DESC;

GROUP BY source collapses all rows with the same source into one row, and aggregates such as COUNT and SUM then work inside each group. Every column in SELECT must either appear in GROUP BY or sit inside an aggregate. Otherwise SQL cannot know which of the many values in a group to show.

For the first quarter of 2026 the result puts direct first with CHF 14,711.70, then paid search with CHF 5,798.60 and organic with CHF 5,716.96. Social comes last with CHF 2,662.37. Read it carefully: source is the source of the visit that led to the order. Of the 273 orders placed from direct visits in that quarter, only 22 were a first order. This table shows where orders arrive, not which channel won the customer.

Grouping by a calculated value

To group by month, compute the month from the date. strftime(order_date, '%Y-%m') formats a date as text such as 2025-07. Because the text starts with the year, sorting it also sorts the months in time order.

• Name the calculated column with AS, for example AS month. In DuckDB you can then write GROUP BY month; in some other databases you repeat the calculation.
• Filter before you group. WHERE removes rows before they are grouped, so cancelled orders never reach the sums.
• Sort the rows the way the chart reads: months by time, categories by size.
• Keep the number of rows small enough to read. Eight regions or twelve months make a good chart; 96 products usually need a top 10 and one row for the rest.

What the year looks like

Grouped by month, delivered revenue in 2025 ranges from CHF 7,963.79 in July to CHF 18,373.89 in December. December did not have the most orders; November did, with 273. December baskets were the largest, at CHF 70.67 on average. The second quarter of 2025 brought CHF 37,265.67.

Orders and revenue do not always rank groups the same way. In 2025 Geneva had 272 delivered orders and Basel 264, yet Basel brought more revenue, CHF 16,079.85 against CHF 15,744.15, because its orders were larger on average. When two values could tell different stories, both columns belong in the table.

This shape matters to Lena in two ways. The second quarter is not a peak season, so flat revenue from April to June would not prove that a new budget failed. And channel results from December should not be compared with results from July without saying so in the memo.