Jeff's SQL Server Blog

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

My Links



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.



Post Categories




More on GROUP BY; Examining SUM(Distinct)

I've written a two part article on using SQL GROUP BY clauses over at  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 ]



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

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

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

# 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

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

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

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

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

# 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

# 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