Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Greg Larsen
Greg Larsen
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1291 Visits: 290
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
bojan.jekic 78340
bojan.jekic 78340
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 105
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".
Tim Curtin
Tim Curtin
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 50
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
DouglasH
DouglasH
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 1469
I think the following sentence is wrong or could be misinterpreted-

If you use an expression in the GROUP BY clause the same exact expression must be used in the SELECT list.


I think it means that a column used in an expression in the GROUP BY can't be in the SELECT list unless the expression itself is used.

My point is that it doesn't have to be in the SELECT list.

for example,
SELECT MAX(SalesDateTime)
FROM dbo.SalesTransaction
GROUP BY CONVERT(CHAR(10),SalesDateTime,101);



or
SELECT COUNT(*)
FROM dbo.SalesTransaction
GROUP BY CONVERT(CHAR(10),SalesDateTime,101);





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search