GROUP BY: Aggregate Interactions

GROUP BY and aggregates work well together, but you can use them alone. The previous chapter examined aggregates without groups. Now let's look at GROUP BY alone and with aggregates.

GROUP BY Without Aggregates

Used without aggregates, GROUP BY is like DISTINCT. It divides a table into groups and returns one row for each group. Remember, whenever you use GROUP BY, each item in the SELECT list has to produce a single value per set. Following are some examples to make the relationship to DISTINCT clearer. First, a listing of all the publishers in the titles table:

SQL
select pub_id
from titles
pub_id
------
1389
1389
0736
1389
0877
0877
0877
1389
1389
1389
0877
0736
0736
0736
0736
0877
0877
0877
[18 rows]

Next, ...

Get Practical SQL Handbook, The: Using SQL Variants, Fourth Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.