Ok, this was a new one for me. I was reading my copy of SQL Server Magazine the other night and found this. Itzik Ben-Gan wrote the article and I wish he had a Blog for me to read. He always writes very good stuff.
So, that being said… What is the “ALL”???
Here is the word from good ole’ F1 (help)
“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.”
VERY BIG NOTE (Also from Help)
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.
So, use at you own risk, but still is very cool:
Example using AdventureWorks Db:
SELECT
‘PurchaseOrders’=COUNT(*)
, emp.LoginID
FROM Purchasing.PurchaseOrderHeader poh
INNER JOIN HumanResources.Employee emp
ON poh.EmployeeID = emp.EmployeeID
WHERE poh.OrderDate BETWEEN ‘1/1/2001′ AND ‘12/31/2001′
GROUP BY emp.LoginID>
Results:
1 adventure-works\eric2
1 adventure-works\erin0
1 adventure-works\frank2
1 adventure-works\fukiko0
1 adventure-works\gordon0
1 adventure-works\linda2
1 adventure-works\mikael0
1 adventure-works\reinout0
Now with the ALL keyword: (Change group by line)
GROUP BY ALL emp.LoginID
>>Results:
0 adventure-works\annette0
0 adventure-works\arvind0
0 adventure-works\ben0
1 adventure-works\eric2
1 adventure-works\erin0
1 adventure-works\frank2
1 adventure-works\fukiko0
1 adventure-works\gordon0
1 adventure-works\linda2
1 adventure-works\mikael0
1 adventure-works\reinout0
0 adventure-works\sheela0
Subscribe Feed (RSS)