BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Grouping

The twenty-eighth part of the SQL Server Programming Fundamentals tutorial adds to the description of the use of aggregate functions within queries. In this article the GROUP BY and HAVING clauses are used to summarise groups of similar result rows.

GROUP BY ALL

The behaviour of the GROUP BY clause can be modified using the ALL keyword. This keyword causes all of the possible groups from a table to be returned, even if the query's WHERE clause filters out all of the results for a particular group. In this case, the group will be displayed but all aggregated values in the row will be null.

GROUP BY ALL has some limitations. Key to these is that it should never be used with remote tables. Additionally, the clause is not supported by Microsoft when used with a WHERE clause. Without such a WHERE clause the addition of the ALL keyword has no meaning. Generally you should not use the ALL variation; it is included in this article only for completeness. Microsoft has indicated that GROUP BY ALL will be removed from future versions of SQL Server.

Filtering Aggregated Results

The results of a query containing grouping can be filtered in two ways. Firstly, a WHERE clause may be used before the GROUP BY clause to filter the results before any aggregate calculations are made. Secondly, you can use the HAVING clause to add criteria that are considered after the grouping and aggregation operations.

The HAVING clause uses a similar syntax to the WHERE clause. The key difference is the ability to apply filtering to calculated columns. For example, if we wish to amend the previous query to only return engineers that have a total stock of more than twenty units we can use the following query:

SELECT
    E.EngineerName,
    S.EngineerId,
    sum(S.UnitsHeld)
FROM
    EngineerStock S
INNER JOIN
    Engineers E
ON
    S.EngineerId = E.EngineerId
GROUP BY
    S.EngineerId,
    E.EngineerName
HAVING
    sum(S.UnitsHeld) > 20

Often both a WHERE clause and a HAVING clause will be used in the same query. For example, the following query lists the engineers who have more than twenty units of stock of 15mm Copper Pipe (15COPIPE). The WHERE clause limits the initial query to the copper pipe item before the grouping occurs. The HAVING clause removes any rows where the total stock calculation gives an answer of twenty or less.

SELECT
    E.EngineerName,
    S.EngineerId,
    sum(S.UnitsHeld)
FROM
    EngineerStock S
INNER JOIN
    Engineers E
ON
    S.EngineerId = E.EngineerId
WHERE
    S.PartNumber = '15COPIPE'
GROUP BY
    S.EngineerId,
    E.EngineerName
HAVING
    sum(S.UnitsHeld) > 20

Sorting Aggregated Results

For the final example we will add a sort order to the above query. When grouping results, ordering may be applied to any of the columns in the query, including those that are calculated by aggregate functions. The query below demonstrates this by ordering the results by the calculated stock total, with the highest stock level first and the lowest last.

SELECT
    E.EngineerName,
    S.EngineerId,
    sum(S.UnitsHeld)
FROM
    EngineerStock S
INNER JOIN
    Engineers E
ON
    S.EngineerId = E.EngineerId
WHERE
    S.PartNumber = '15COPIPE'
GROUP BY
    S.EngineerId,
    E.EngineerName
HAVING
    sum(S.UnitsHeld) > 20
ORDER BY
    sum(S.UnitsHeld) DESC
28 May 2009