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 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?

Filed under: Blog Tagged: syndicated, T-SQL