Grouping Rows in Query Results

See Also

If you want to create subtotals or show other summary information for subsets of a table, you create groups using an aggregate query. Each group summarizes the data for all the rows in the table that have the same value.

For example, you might want to see the average price of a book in the titles table, but break the results down by publisher. To do so, you group the query by publisher (for example, pub_id). The resulting query output might look like this:

When you group data, you can display only summary or grouped data, such as:

The values of the grouped columns (those that appear in the GROUP BY clause). In the example above, pub_id is the grouped column.
Values produced by aggregate functions such as SUM( ) and AVG( ). In the example above, the second column is produced by using the AVG( ) function with the price column.

You cannot display the contents of individual columns. For example, if you group only by publisher, you cannot also display individual titles in the query. Therefore, if you add columns to the query output, the Query Designer automatically adds them to the GROUP BY clause of the statement in the SQL pane. If you want a column to be summarized instead, you can specify an aggregate function for that column.

If you group by more than one column, each group in the query shows the summary values for all grouping columns.

For example, the following query against the titles table groups by publisher (pub_id) and also by book type (type). The query results are ordered by publisher and show summary information for each different type of book that the publisher produces:

SELECT pub_id, type, SUM(price) Total_price
FROM titles
GROUP BY pub_id, type

The resulting output might look like this:

To group rows

Start the query by adding the table you want to summarize to the Diagram pane.
From the Query menu, choose Group By. The Query Designer adds a Group By column to the grid in the Grid pane.
Add the column you want to group to the Grid pane. If you want the column to appear in the query output, be sure that the column is marked for output.
The Query Designer adds a GROUP BY clause to the statement in the SQL pane. For example, the SQL statement might look like this:
```
SELECT pub_id
FROM titles
GROUP BY pub_id
```
Add the column you want to summarize to the Grid pane. Be sure that the column is marked for output.
In the Group By grid column for the summary data column, select the aggregate function that you want to use to summarize the data.
The Query Designer automatically assigns a column alias to the column you are summarizing to create a useful column heading in query output. For more details, see Creating Column Aliases.

The corresponding statement in the SQL pane might look like this:
```
SELECT   pub_id, SUM(price) AS Expr1
FROM     titles
GROUP BY pub_id
```