GROUP BYQuery Techniques
Buy Me A Coffee

Aggregate rows sharing common values.

Aggregates rows into groups, collapsing them to one row per group while enabling aggregate functions like COUNT and SUM. All non-aggregated SELECT columns must appear in the GROUP BY clause. Inconsistencies between SQL standards and MySQL default behavior can produce unexpected values.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all ROLLUP extension
PostgreSQL ✓ Supported all Standard + CUBE/ROLLUP
SQL Server ✓ Supported 2005 Grouping sets
Oracle ✓ Supported all Grouping sets/CUBE
SQLite ✓ Supported all Basic only

Details

Combine rows with same keys for aggregation.

Standard Syntax

SELECT col, COUNT(*) FROM tbl GROUP BY col HAVING COUNT(*) > 1;

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since 2005 Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

ROLLUP extension

SELECT col, COUNT(*) FROM tbl GROUP BY col WITH ROLLUP;

PostgreSQL

Standard + CUBE/ROLLUP

SELECT col, COUNT(*) FROM tbl GROUP BY CUBE(col1,col2);

SQL Server

Grouping sets

SELECT col, COUNT(*) FROM tbl GROUP BY GROUPING SETS((col),(col2));

Oracle

Grouping sets/CUBE

SELECT col, COUNT(*) FROM tbl GROUP BY ROLLUP(col1,col2);

SQLite

Basic only

SELECT col, COUNT(*) FROM tbl GROUP BY col HAVING COUNT(*)>1;