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.
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 p.id = ps.id 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 p.id = ps.id 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?