Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stairway to T-SQL DML Level 7: Summarizing Data Using a Simple GROUP BY Clause Expand / Collapse
Author
Message
Posted Monday, November 14, 2011 7:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, April 12, 2014 6:50 AM
Points: 1,040, Visits: 274
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
Posted Wednesday, January 04, 2012 3:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 3:02 AM
Points: 1, Visits: 86
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
Posted Wednesday, January 04, 2012 7:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, January 31, 2014 7:09 AM
Points: 21, Visits: 34
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 »

Add to briefcase

Permissions Expand / Collapse