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 SQLServerCentral.com 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 twitter.com/way0utwest

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

giftsa

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:

giftsb

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

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...