How does GROUP BY work?
GROUP BY
returns a single row for each unique combination of the GROUP BY
fields. So in your example, every distinct combination of (a1, a2)
occurring in rows of Tab1
results in a row in the query representing the group of rows with the given combination of group by field values . Aggregate functions like SUM()
are computed over the members of each group.
GROUP BY
groups all the identical records.
SELECT COUNT(ItemID), City
FROM Orders
GROUP BY City;
----------------------------------------
13 Sacrmento
23 Dallas
87 Los Angeles
5 Phoenix
If you don't group by City
it will just display the total count of ItemID
.
GROUP BY
returns one row for each unique combination of fields in the GROUP BY
clause. To ensure only one row, you would have to use an aggregate function - COUNT
, SUM
, MAX
- without a GROUP BY
clause.
Analogously, not technically, to keep in mind its logic, it can be thought each grouped field having some rows is put per different table, then the aggregate function carries on the tables individually.
Ben Forta conspicuously states the following saying.
The
GROUP BY
clause instructs the DBMS to group the data and then perform the aggregate (function) on each group rather than on the entire result set.Aside from the aggregate calculation statements, every column in your
SELECT
statement must be present in theGROUP BY
clause.The
GROUP BY
clause must come after anyWHERE
clause and before anyORDER BY
clause.
My understanding reminiscent of his saying is the following.
As is
DISTINCT
keyword, each field specified throughGROUP BY
is thought as grouped and made unique at the end of the day. The aggregate function is carried out over each group, as happened in SuL's answer.