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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Gregory Larsen » Stairway to T-SQL DML Level 7: Summarizing...

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

Rate Topic

Display Mode

Topic Options

Author

Message

Greg Larsen

Posted Monday, November 14, 2011 7:22 AM

Ten Centuries

Group: General Forum Members
Last Login: Saturday, January 19, 2013 8:28 AM
Points: 1,038, Visits: 255

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

Gregory A. Larsen, MVP

Need SQL Server Examples check out my website at http://www.sqlserverexamples.com

Post #1205128

bojan.jekic 78340

Posted Wednesday, January 04, 2012 3:14 AM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 4:57 AM
Points: 1, Visits: 81

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,Shelf

In 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".

Post #1229747

Tim Curtin

Posted Wednesday, January 04, 2012 7:57 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, January 08, 2013 10:40 PM
Points: 20, Visits: 28

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 StoreSalesAmount
FROM 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 StoreSalesAmount
FROM 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

Post #1229946

« Prev Topic | Next Topic »

Permissions