GROUP BY and ALL

Transact-SQL provides the ALL keyword in the GROUP BY clause. ALL is meaningful only when the SELECT statement also includes a WHERE clause.

Note:
GROUP BY ALL will be removed in a future version of Microsoft SQL Server. Avoid using GROUP BY ALL in new development work, and plan to modify applications that currently use it.

GROUP BY ALL should not be used with queries that access remote tables. It is not supported in such queries if there is also a WHERE clause in the query, and without a WHERE clause it adds no value.

If you use ALL, the query results include all groups produced by the GROUP BY clause, even if some of the groups have no rows that meet the search conditions. Without ALL, a SELECT statement that includes GROUP BY does not show groups for which no rows qualify.

The first example produces groups only for orders with quantities > 10.

The second example produces groups for all orders.

The column that holds the aggregate value (the average price) is NULL for groups that lack qualifying rows.

USE AdventureWorks;
GO
SELECT ProductID, AVG(UnitPrice) AS 'Average Price'
FROM Sales.SalesOrderDetail
WHERE OrderQty > 10
GROUP BY ProductID
ORDER BY ProductID ;
GO

Here is the result set.

ProductID   Average Price
707         20.7765
708         20.7482
709         6.175
711         20.7285
712         5.7592
714         32.05
.............................
(114 row(s) affected)

-- Using GROUP BY ALL
USE AdventureWorks;
GO
SELECT ProductID, AVG(UnitPrice) AS 'Average Price'
FROM Sales.SalesOrderDetail
WHERE OrderQty > 10
GROUP BY ALL ProductID
ORDER BY ProductID ;
GO

Here is the result set.

ProductID   Average Price
707         20.7765
708         20.7482
709         6.175
710         NULL
711         20.7285
712         5.7592
..............................
Warning: Null value is eliminated by an aggregate or other SET operation.

(266 row(s) affected)