How does GROUP BY work?

Question

Suppose I have a table Tab1 with attributes - a1, a2, ... etc. None of the attributes are unique.

What will be the nature of the following query? Will it return a single row always?

SELECT a1, a2, sum(a3) FROM Tab1 GROUP BY a1, a2

Answer 1

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.

Answer 2

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.

Answer 3

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 the GROUP BY clause.

The GROUP BY clause must come after any WHERE clause and before any ORDER BY clause.

My understanding reminiscent of his saying is the following.

As is DISTINCT keyword, each field specified through GROUP 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.

Answer 4

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.