SELECT results by the values of the specified columns or expressions. The aggregate functions (
AVG) are often used together with
GROUP BY to perform calculations in each group.
SELECT -- columns that are not used in AGGREGATE_FUNCTION column1, column2, ... column_n -- and must be included into GROUP BY AGGREGATE_FUNCTION -- the function COUNT, SUM, MAX, MIN, SUM, or AVG (aggregate_column) -- argument of the aggregate function FROM table GROUP by -- if you group by an expression, set the expression name column1, column2, ... column_n -- using AS inside GROUP BY, -- and then use this name inside SELECT
By default, the aggregate functions don't account for
NULL in their arguments.
SELECT key, COUNT(*) FROM my_table GROUP BY key;
SELECT double_key, COUNT(*) FROM my_table GROUP BY key + key AS double_key;
ROLLUP, CUBE, and GROUPING SETS
The results of calculating the aggregate function as subtotals for the groups and overall totals over individual columns or whole table.
SELECT c1, c2, -- the columns to group by AGGREGATE_FUNCTION(c3) AS outcome_c -- an aggregate function (SUM, AVG, MIN, MAX, COUNT) FROM table_name GROUP BY GROUP_BY_EXTENSION(c1, c2) -- an extension of GROUP BY: ROLLUP, CUBE, or GROUPING SETS
ROLLUPgroups the column values in the order they are listed in the arguments (strictly from left to right), generates subtotals for each group and the overall total.
CUBEgroups the values for every possible combination of columns, generates the subtotals for each group and the overall total.
GROUPING SETSsets the groups for subtotals.
You can combine ROLLUP,
GROUPING SETS, separating them by commas.
The values of columns not used in calculations are replaced with
NULL in the subtotal. In the overall total, the values of all columns are replaced by
GROUPING: A function that allows you to distinguish the source
NULL values from the
NULL values added while calculating subtotals and overall totals.
GROUPING returns a bit mask:
NULLis used for the original empty value.
NULLis added for a subtotal or overall total.
SELECT column1, column2, column3, CASE GROUPING( column1, column2, column3, ) WHEN 1 THEN "Subtotal: column1 and column2" WHEN 3 THEN "Subtotal: column1" WHEN 4 THEN "Subtotal: column2 and column3" WHEN 6 THEN "Subtotal: column3" WHEN 7 THEN "Grand total" ELSE "Individual group" END AS subtotal, COUNT(*) AS rows_count FROM my_table GROUP BY ROLLUP( column1, column2, column3 ), GROUPING SETS( (column2, column3), (column3) -- if you add here (column2) as well, then together -- those ROLLUP and GROUPING SETS would produce the result -- similar to CUBE ) ;
Applying aggregate functions only to distinct values of the column.
DISTINCT to calculated values is not currently implemented. For this purpose, use a subquery or the clause
GROUP BY ... AS ....
SELECT key, COUNT (DISTINCT value) AS count -- top 3 keys by the number of unique values FROM my_table GROUP BY key ORDER BY count DESC LIMIT 3;
You can also use
DISTINCT to fetch unique rows using
SELECT key FROM my_table GROUP BY key HAVING COUNT(value) > 100;