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