:::: MENU ::::

Using GROUP BY and GROUP BY ALL in T-SQL scripts

  • Jan 29 / 2009
  • 0
dbDigger, Performance Tunning and Optimization, T-SQL Tips and Tricks

Using GROUP BY and GROUP BY ALL in T-SQL scripts

As we know that GROUP BY clause is used to summarize the given columns in select statement. Example of using GROUP BY in AdventureWorks database is as follows. In this example we will calculate the total amount due of each date between July 01 2001 to July 31 2001.

 
USE AdventureWorks  
 GO  
 SELECT OrderDate,SUM(TotalDue) TotalDueByOrderDate  
 FROM Sales.SalesOrderHeader  
 WHERE OrderDate BETWEEN '7/1/2001' AND '7/31/2001'  
 GROUP BY OrderDate  

As a result 31 rows are returned in following format.

ResultOfGroupBy

Now If there is a situation that we have to include all row values in result, even if these do not qualify the WHERE clause, then we have to use GROUP BY ALL. Running above script with GROUP BY ALL provides different result set.

 
USE AdventureWorks  
 GO  
 SELECT OrderDate,SUM(TotalDue) TotalDueByOrderDate  
 FROM Sales.SalesOrderHeader  
 WHERE OrderDate BETWEEN '7/1/2001' AND '7/31/2001'  
 GROUP BY ALL OrderDate  

ResultOfGroupByAll
Now we get 1124 rows. Even the rows that do not qualify the where clause have been returned but not included in calculation.

Consult us to explore the Databases. Contact us