More on GROUP BY; Examining SUM(Distinct)

Tue Jul 31, 2007 by Jeff Smith in t-sql, links, group-by

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!

Legacy Comments


Aneesh Kumar 2008-07-18	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

Sandeep 2010-01-31	re: More on GROUP BY; Examining SUM(Distinct) Hi cannot open the part 1 and Part 2 sites

Edwin 2010-03-09	re: More on GROUP BY; Examining SUM(Distinct) Good stuff!

David 2010-03-10	re: More on GROUP BY; Examining SUM(Distinct) Thanks! Exactly the problem I was having.

Daniel 2010-04-22	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)

Andrew 2010-05-08	re: More on GROUP BY; Examining SUM(Distinct) thanks! saved my butt here.