GROUP BY and Aggregate Functions

~14 min · queries, aggregation

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Collapsing rows into summaries

GROUP BY is the steamroller — it collapses rows into groups, then runs an aggregate (COUNT, SUM, AVG, MIN, MAX) over each group. You lose individual row identity; you gain summaries.

The strict rule

Every column in SELECT must appear in GROUP BY or be inside an aggregate. PostgreSQL enforces this rigorously — unlike MySQL, which silently picks a random value (and is wrong about it). The strictness is a feature; it forces you to think about what each row of the result actually means.

DATE_TRUNC for time bucketing

For time-series aggregation, DATE_TRUNC('day', timestamp_col) truncates each timestamp to the start of its day (or week / month / hour / minute). Group by the truncated value to get clean buckets.

Code

Whole-table summary·sql

SELECT COUNT(*)         AS total_orders,
       SUM(total)       AS revenue,
       AVG(total)       AS avg_order,
       MIN(total)       AS smallest,
       MAX(total)       AS largest,
       COUNT(DISTINCT customer_id) AS unique_customers
FROM   orders;

GROUP BY per group·sql

SELECT u.name,
       COUNT(o.id)         AS order_count,
       SUM(o.total)        AS total_spent,
       ROUND(AVG(o.total), 2) AS avg_order
FROM   users u
JOIN   orders o ON o.user_id = u.id
GROUP  BY u.name
ORDER  BY total_spent DESC;

Time-bucketing with DATE_TRUNC·sql

SELECT DATE_TRUNC('month', placed_at) AS month,
       COUNT(*)                       AS orders,
       SUM(total)                     AS revenue
FROM   orders
GROUP  BY DATE_TRUNC('month', placed_at)
ORDER  BY month;

-- Group by hour of day for traffic analysis
SELECT EXTRACT(HOUR FROM placed_at) AS hour_of_day,
       COUNT(*) AS orders
FROM   orders
GROUP  BY 1
ORDER  BY 1;

External links

PostgreSQL GROUP BY

Exercise

Build a 'monthly active users' query: count distinct users per month for the last year. Then extend it to include the previous month's count side-by-side using a self-join or a window function (sneak peek at lesson 15).

Progress

Progress is local-only — sign in to sync across devices.

← PreviousRIGHT JOIN, FULL JOIN, and Cross Joins Next →HAVING: Filtering After Aggregation

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.