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
| Database System | Support Status | Since Version | Notes |
|---|---|---|---|
| MySQL | Native | all | ROLLUP extension |
| PostgreSQL | Native | all | Standard + CUBE/ROLLUP |
| SQL Server | Native | 2005 | Grouping sets |
| Oracle | Native | all | Grouping sets/CUBE |
| SQLite | Native | 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: Native in all listed versions
PostgreSQL: Native in all listed versions
SQL Server: Native since 2005
Oracle: Native in all listed versions
SQLite: Native in all listed versions
Per-Database Syntax & Notes
MySQL Native syntax
ROLLUP extension
SELECT col, COUNT(*)
FROM tbl
GROUP BY col WITH ROLLUP;
PostgreSQL Native syntax
Standard + CUBE/ROLLUP
SELECT col, COUNT(*)
FROM tbl
GROUP BY CUBE(col1,col2);
SQL Server Native syntax
Grouping sets
SELECT col, COUNT(*)
FROM tbl
GROUP BY GROUPING SETS((col),(col2));
Oracle Native syntax
Grouping sets/CUBE
SELECT col, COUNT(*)
FROM tbl
GROUP BY ROLLUP(col1,col2);
SQLite Native syntax
Basic only
SELECT col, COUNT(*)
FROM tbl
GROUP BY col
HAVING COUNT(*)>1;