SQLServerCentral / Discuss Content Posted by Gregory Larsen / Article Discussions / Article Discussions by Author / Stairway to T-SQL DML Level 7: Summarizing Data Using a Simple GROUP BY Clause / Latest Posts

RE: Stairway to T-SQL DML Level 7: Summarizing Data Using a Simple GROUP BY Clause

Tim Curtin — Wed, 04 Jan 2012 07:57:08 GMT

Good article...One comment though...I would not filter on a row value in a having statement, the code is wasting CPU cycles and too much memory swapping.The last example would be an illustration of that.SELECT StoreName ,SUM(TotalSalesAmount) AS StoreSalesAmountFROM dbo.SalesTransaction GROUP BY StoreName HAVING StoreName LIKE '%Outlet%' <==== NOT GOOD OR StoreName LIKE '%Books%';This should be better written like:SELECT StoreName ,SUM(TotalSalesAmount) AS StoreSalesAmountFROM dbo.SalesTransaction WHERE StoreName LIKE '%Outlet%' <=== This way the row is not even loaded in to memory to be aggregated. OR StoreName LIKE '%Books%';GROUP BY StoreName

RE: Stairway to T-SQL DML Level 7: Summarizing Data Using a Simple GROUP BY Clause

bojan.jekic 78340 — Wed, 04 Jan 2012 03:14:04 GMT

I noticed an interesting feature when I do "Grouping by Two Columns" on table with PrimaryKey (Clustered Index Unique):select ProductID,Shelf,sum(Quantity)from Production.ProductInventory group by ProductID,ShelfIn this case, the result is automatically sorted by the second column from GroupBy clause “Shelf”. Is not that strange? In certain situations, the ExecutionPlan shows that SQL Engne executes the illogical/invert sort. Why executes any sort?This is not the case when we do "Grouping by Tree Columns or More".

Stairway to T-SQL DML Level 7: Summarizing Data Using a Simple GROUP BY Clause

Greg Larsen — Mon, 14 Nov 2011 07:22:42 GMT

Comments posted to this topic are about the item [B]Stairway to T-SQL DML Level 7: Summarizing Data Using a Simple GROUP BY Clause[/B]