GROUP BY
GROUP BY splits rows into groups so each group gets its own aggregate result.
In this page:
Syntax
SELECT group_column, aggregate_function(column)
FROM table_name
GROUP BY group_column;GROUP BY
Every selected column must be either in GROUP BY or inside an aggregate function. You can group by several columns and by expressions.
Group order is not guaranteed, so add ORDER BY.
PostgreSQL allows grouping by column position and by alias, though names are clearer.
Note:
Add ORDER BY after GROUP BY for a predictable result order.
Example: GROUP BY
CREATE TABLE sales (id INTEGER PRIMARY KEY, item TEXT, region TEXT, qty INTEGER);
INSERT INTO sales VALUES (1,'pen','N',5),(2,'book','N',1),(3,'pen','S',10),(4,'book','S',3),(5,'lamp','N',2);
SELECT item, SUM(qty) AS total_qty, COUNT(*) AS orders FROM sales GROUP BY item ORDER BY item;
SELECT region, item, SUM(qty) AS total_qty FROM sales GROUP BY region, item ORDER BY region, item;
-- Output:
-- item | total_qty | orders
-- book | 4 | 2
-- lamp | 2 | 1
-- pen | 15 | 2
-- region | item | total_qty
-- N | book | 1
-- N | lamp | 2
-- N | pen | 5
-- S | book | 3
-- S | pen | 10Login to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Selecting columns that are neither grouped nor aggregated
- Expecting sorted output
- Grouping by too many columns
Chapter Summary
- GROUP BY makes one row per group
- Selected columns must be grouped or aggregated
- Multiple columns give finer groups
- Add ORDER BY for order
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: