GROUP BY Syntax

Here's what GROUP BY looks like in the context of a SELECT statement:

SELECT select_list
FROM table_list
[ WHERE conditions ]
[ GROUP BY group_by_list ]
[ ORDER BY order_by_list ]

In most SQL dialects, every item in the GROUP BY list must appear in the SELECT list—you can make groups only out of things you select. Systems vary in what they allow in a GROUP BY list, in addition to column names: More complex expressions, display labels, and SELECT list position numbers are all possibilities. Following is an example with a single column in the GROUP BY clause. The query figures how many books each publisher has.

select pub_id, count(title_id)
from titles
group by pub_id
pub_id count(titles.title_id)
====== =====================
1389                       6
0736                       6
0877                       6
[3 rows]

You include the grouping column (pub_id) as well as the aggregate (COUNT) in the SELECT list. All the rows that make up the first group have 1389 in the pub_id column; all those in the second have 0736; and all those in the third have 0877. The COUNT generates a single value (the number of titles) for each group. Both items in the SELECT list (the publishers and the COUNT) are single valued per set—there is only one publisher and one title total for each group.

Groups Within Groups

Just as you can sort by multiple items, so can you form groups within groups. Separate the grouping elements with commas, and go from large groups to progressively smaller ones.

select pub_id, type, count(title)
from titles
group by pub_id, type
pub_id type         count(titles.title)
====== ============ ===================
1389   popular_comp                   3
1389   business                       3
0736   psychology                     5
0877   mod_cook                       2
0877   trad_cook                      3
0736   business                       1
0877   (NULL)                         1
[7 rows]

This example is much the same as the previous one, but it uses nested groups. Here are the steps your system goes through with nested groups.