Sql Server Tutorial: Difference between group by and group by all

First, consider the following GROUP BY query, which doesn't use the ALL option:



SELECT City, COUNT(*) AS NumEmps
FROM dbo.Employees
GROUP BY City;

This query groups employees by city and returns the city and count of employees for each group, as Table1 shows. Suppose you want to consider employees hired only in or after 1993. You just add a simple filter on the HireDate column, as the WHERE clause in the following query shows:

SELECT City, COUNT(*) AS NumEmps
FROM dbo.Employees
WHERE HireDate >= '19930101'
GROUP BY City;

This query filters out employees hired before 1993, groups the remaining employees by city, then returns the city and count of employees for each group, as Table 2 shows.

Notice that the query doesn't return Kirkland and Tacoma, because no employees from these cities were hired in or after 1993.Typically, the GROUP BY clause operates only on rows that remain after you apply the WHERE filter. However, by adding the ALL option to the GROUP BY clause, you can generate empty groups—or groups of zero rows—for cities that the WHERE clause filters out. Table 3 shows the results of adding the ALL option to the GROUP BY clause:

SELECT City, COUNT(*) AS
NumEmps FROM dbo.Employees
WHERE HireDate >= '19930101'
GROUP BY ALL City;

Kirkland and Tacoma now appear in the query results, each with a count of 0, showing that no employees from these cities were hired in or after 1993. Including these cities in the result set indicates that the Employees table contains employees from these cities—just not employees hired in or after 1993.

To know more about such queries visit us at Bestdotnettraining