Home > Data Access / ADO.NET > Structured Query Language (SQL) Basics > SQL Group By: Grouping Data in Qquery Results

---

You can combine records into groups based on values in one or more columns.

The GROUP BY clause combines records with identical values in the specified column list into a single record.

SELECT * FROM tableName WHERE filterCondition GROUP BY groupColumn

in which groupColumn can be:

• the name of a regular table column
• a column that includes an SQL aggregate function
• a numeric expression indicating the location of the column in the result table ( the leftmost column number is 1 ).

Depending on the data provider, GROUP BY can include more than one column as the groupColumn. The order of column names in groupColumn determines the grouping levels from highest to lowest.

A summary value is created for each record if you include an SQL aggregate function.

All columns in the SELECT column list must either be included in the GROUP BY clause or be included as arguments to an SQL aggregate function.

Use the WHERE clause to exclude rows you do not want grouped, and use the HAVING clause ( see below ) to filter records after they have been grouped.

Null values in GROUP BY columns are grouped and are not omitted. However, Null values are not evaluated in any SQL aggregate function.

Unless it contains Memo or OLE Object data, a column in the GROUP BY column list can refer to any column in any table listed in the FROM clause, even if the column is not included in the SELECT statement, provided the SELECT statement includes at least one SQL aggregate function.

You cannot group on Memo or OLE Object columns.

Using GROUP BY (Example 1) Run Sample | View Source

Other xamples of using GROUP BY can be found in the SQL aggregate functions.

The HAVING clause specifies a filter condition that groups must meet to be included in the query results.

SELECT * FROM tableName WHERE filterCondition 
   GROUP BY groupColumn HAVING filterCondition

Using GROUP BY ... HAVING (Example 1) Run Sample | View Source

After GROUP BY combines records, HAVING specifies which of the grouped records are to be displayed, using an expression similar to a WHERE clause to determine which grouped records to display.

HAVING can only be used with GROUP BY. It can include as many filter conditions as needed, connected with the AND or OR operator. You can also use NOT to reverse the value of a logical expression.

A HAVING clause without a GROUP BY clause acts like a WHERE clause. You can use column functions in the HAVING clause. Use a WHERE clause for faster performance if your HAVING clause contains no column functions.

SQL ORDER BY: Sorting Data In Query Results