Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

The Voice of the DBA

Steve Jones is the editor of and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at

Counting the Existing, and Missing, Rows

I saw this as a problem recently from someone and thought it would make a good post. As I was building a quick solution, someone else posted theirs, but I decided to save mine as a blog post. It was a good, quick, T-SQL exercise for me to work on.

I decided to take the Christmas season and use that as my example. My wife and I buy presents for the family and we try to understand what we’ve bought each year to balance out our efforts for each kid.

My setup:

CREATE TABLE People ( id INT , firstname VARCHAR(20) ); CREATE TABLE presents ( id INT , present VARCHAR(20) , value NUMERIC(6, 2) ); INSERT INTO People VALUES ( 1, 'Kyle' ), ( 2, 'Delaney' ), ( 3, 'Kendall' ), ( 4, 'Tia' ), ( 5, 'Steve' ) INSERT INTO presents VALUES ( 1, 'Book', 10 ), ( 1, 'Fire', 157 ), ( 3, 'Book', 8 ), ( 3, 'tablet', 162 ), ( 3, 'hat', 12 ), ( 4, 'bracelet', 80 )

I’ve modeled this with two tables: one holding people and one with the presents. I need to join them together and see what I’ve bought.

SELECT p.firstname , presentcount = COUNT(ps.present) , value = ISNULL(SUM( ps.value), 0) FROM people p INNER JOIN presents ps ON = GROUP BY p.firstname


That gives me a count of gifts and money spent


The problem is that it doesn’t let me know what people I haven’t bought for. For that I need to change to an outer join, in this case I’ll choose a left outer join since the people table is the one I need all rows from:

SELECT p.firstname , presentcount = COUNT(ps.present) , value = ISNULL(SUM( ps.value), 0) FROM people p LEFT OUTER JOIN presents ps ON = GROUP BY p.firstname

Now I can see that Kendall and Steve haven’t received any presents yet. More work to be done:


Outer joins are a quick way to find issues, but be sure you understand how they work. In this simple case, it’s an easy change.

Now this looks like software I might actually use. Perhaps this would make a good project for me?

Filed under: Blog Tagged: syndicated, T-SQL


Leave a comment on the original post [, opens in a new window]

Loading comments...