Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

More on GROUP BY; Examining SUM(Distinct)

I've written a two part article on using SQL GROUP BY clauses over at SQLTeam.com.  It's always a common topic of discussion and confusion amongst beginner and intermediate SQL programmers alike, so I thought I'd write a fairly long and hopefully comprehensive piece that takes a common summary report request and works towards the solution step-by-step.
  • Part I:  Intro to GROUP BY; Duplicates caused by JOINS; Identifying "Virtual Primary Keys"; Using COUNT(Distinct)
  • Part II:  Examining SUM(Distinct); GROUPING before JOINING; Using Derived Tables
I tried to focus on what happens when you join two tables and then try to group and aggregate the results, and where and how to do this to avoid aggregating duplicate values.   As always, I attempt to demonstrate breaking down the larger problem into smaller, simpler parts and then putting those pieces together to produce the final result.

Perhaps most importantly: if you've ever used SUM(Distinct) in your code in an attempt to handle duplicates, be sure to read Part II.  As Inigo Montoya would say: I do not think it means what you think it means!

Print | posted on Tuesday, July 31, 2007 12:44 PM | Filed Under [ T-SQL Links GROUP BY ]

Feedback

Gravatar

# re: More on GROUP BY; Examining SUM(Distinct)

Please send me the sql command logic for max date of a group from a table

Thanks
7/18/2008 6:16 AM | Aneesh Kumar
Gravatar

# re: More on GROUP BY; Examining SUM(Distinct)

Hi cannot open the part 1 and Part 2 sites
1/31/2010 11:10 AM | Sandeep
Gravatar

# re: More on GROUP BY; Examining SUM(Distinct)

Good stuff!
3/9/2010 6:18 AM | Edwin
Gravatar

# re: More on GROUP BY; Examining SUM(Distinct)

Thanks! Exactly the problem I was having.
3/10/2010 3:30 PM | David
Gravatar

# re: More on GROUP BY; Examining SUM(Distinct)

Really useful post. I had to put sum(distinct) into one of our reports today as a dirty little hack, thanks for showing me how to avoid it in the future (and for the immediate revision of what I wrote today)
4/22/2010 5:07 PM | Daniel
Gravatar

# re: More on GROUP BY; Examining SUM(Distinct)

thanks! saved my butt here.
5/8/2010 11:05 PM | Andrew
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET