When you type a T-SQL Query, you often want to group the data. Today I came across a neat trick for grouping: the GROUP BY ALL option. It may be a bit hard to explain what it does exactly, but a few examples should make it clear!

With GROUP BY ALL, all rows are grouped, even when they fall outside the dataset because you filtered them out in the WHERE. Take the Query below, for example:

SQL
SELECT  CAST(DT.CalendarYear AS INT) * 100 + CAST(DT.WeekNumberOfYear AS INT) AS WeekNummer,
SUM(FIS.OrderQuantity) AS AantalOrders,
SUM(FIS.SalesAmount) AS Omzet
FROM    FactInternetSales AS FIS
LEFT OUTER JOIN DimTime AS DT ON FIS.OrderDateKey = DT.TimeKey
WHERE ProductKey = 1
GROUP BY CAST(DT.CalendarYear AS INT) * 100 + CAST(DT.WeekNumberOfYear AS INT)
ORDER BY CAST(DT.CalendarYear AS INT) * 100 + CAST(DT.WeekNumberOfYear AS INT)

This dataset returns all week numbers with the sales for Product 1 next to them. If a week has no sales, the week is not shown.

group_by_all_02

If we add GROUP BY ALL………

SQL
SELECT  CAST(DT.CalendarYear AS INT) * 100 + CAST(DT.WeekNumberOfYear AS INT) AS WeekNummer,
SUM(FIS.OrderQuantity) AS AantalOrders,
SUM(FIS.SalesAmount) AS Omzet
FROM    FactInternetSales AS FIS
LEFT OUTER JOIN DimTime AS DT ON FIS.OrderDateKey = DT.TimeKey
WHERE ProductKey = 1
GROUP BY ALL CAST(DT.CalendarYear AS INT) * 100 + CAST(DT.WeekNumberOfYear AS INT)
ORDER BY CAST(DT.CalendarYear AS INT) * 100 + CAST(DT.WeekNumberOfYear AS INT)

……then you also see the weeks that are in factinternetsales but have no sales for product 1:

group_by_all_01

If we add one more line to the WHERE (year > 2001)….

SQL
SELECT  CAST(DT.CalendarYear AS INT) * 100 + CAST(DT.WeekNumberOfYear AS INT) AS WeekNummer,
SUM(FIS.OrderQuantity) AS AantalOrders,
SUM(FIS.SalesAmount) AS Omzet
FROM    FactInternetSales AS FIS
LEFT OUTER JOIN DimTime AS DT ON FIS.OrderDateKey = DT.TimeKey
WHERE ProductKey = 1
AND DT.CalendarYear > 2001
GROUP BY ALL CAST(DT.CalendarYear AS INT) * 100 + CAST(DT.WeekNumberOfYear AS INT)
ORDER BY CAST(DT.CalendarYear AS INT) * 100 + CAST(DT.WeekNumberOfYear AS INT)

…. then you do see all the weeks from 2001, but you find NULL in the other columns. This does not mean that the value is empty, but that the value is not shown (nice and confusing…).

group_by_all_03

Frequently asked questions

What does GROUP BY ALL do in T-SQL?

GROUP BY ALL groups all rows, including the rows you filtered out with the WHERE. So you also see groups for which the WHERE leaves no data.

How do I also show weeks without sales in a T-SQL query?

Put ALL after GROUP BY. In the example you then also see the weeks from FactInternetSales with no sales for product 1. Without ALL, those weeks are not shown.

Why do I see NULL in the columns with GROUP BY ALL?

For groups that the WHERE filtered out, you get NULL in the other columns. This does not mean the value is empty, but that the value is not shown.

What is the difference between GROUP BY and GROUP BY ALL?

GROUP BY only shows groups with rows that pass the WHERE. GROUP BY ALL also shows the groups that the WHERE filtered out. So a week without sales for the product is only missing without ALL.

What happens with GROUP BY ALL when I add a year filter to the WHERE?

In the example with year > 2001 you still see all the weeks from 2001. The other columns of those weeks hold NULL.

Comments

Your email address is not shown. A photo comes from Gravatar, if you have one.

  1. Calculating running totals

    SQL Server24 May 2011 · 5.3k reads
    5.3k

  2. Calculate Weeks Between Two Dates using DateDiff

    SQL Server30 Mar 2012 · 50.2k reads
    50.2k

  3. Script to count nr of rows in all tables

    SQL Server14 May 2012 · 4.2k reads
    4.2k
(3 rows affected)SQL Server