GROUP BY is a command in the SQL relational database standard for collapsing a group of rows that share common field value(s) into a single row. Aggregate functions can be performed on other fields in the group, such as SUM() or AVG(), to collate related data into a single value.
About
The GROUP BY
statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
Aggregate functions can be performed on other fields in the group, such as SUM() or AVG(), to collate related data into a single value.
Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
MySQL Handling of GROUP BY
In standard SQL, a query that includes a GROUP BY
clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY
clause. For example, this query is illegal in standard SQL because the name column in the select list does not appear in the GROUP BY
:
SELECT o.custid, c.name, MAX(o.payment)
FROM orders AS o, customers AS c
WHERE o.custid = c.custid
GROUP BY o.custid;
For the query to be legal, the name column must be omitted from the select list or named in the GROUP BY
clause.
GROUP BY (Aggregate) Functions