Count Join with Where 2

[HestonJames]

Hello Guys,

I'm looking for some help on this query:

Select	Account_Type.Name,
		Count(Account.Account_ID) As Accounts
From	Account_Type
Left Outer Join	Account On Account_Type.Account_Type_ID = Account.Account_Type_ID
Group By Account_Type.Name

This runs just fine and produces a fair result, it gives me a full list of all the different account types with a count of how many accounts are of that particular type. It even lists account types which don't have any accounts under them with a count of 0... perfect!

What I wish to do is add a condition so that it only counts accounts which are from a particular region. So I've amended the query like so:

Select	Account_Type.Name,
		Count(Account.Account_ID) As Accounts
From	Account_Type
Left Outer Join	Account On Account_Type.Account_Type_ID = Account.Account_Type_ID
[b]Where	Account.Region_Id = '3236f8f0-2c55-11df-8a39-0800200c9a66'[/b]
Group By Account_Type.Name

Now, this will only display account types which have accounts under them whereas before it would list ALL account types, regardless of whether it had accounts under it.

Can anyone suggest what I'm doing wrong?

Thanks,

Heston

 

[r937]

what is wrong is that you have put the condition into the WHERE clause

for account types which have no accounts, the LEFT OUTER JOIN puts NULL into the columns of the result set which come from the account table

but then the WHERE clause requires that one of those columns be equal to a certain value, so that row (an unmatched account type) is filtered out

the solution is to put the condition into the ON clause of the join

change the word WHERE to AND and bob's your uncle



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[gmmastros]

You accidentally turned your left join in to an inner join. This is a common mistake. To fix your problem, simply put the where clause condition in to the ON clause. Like this:

Select  Account_Type.Name,
        Count(Account.Account_ID) As Accounts
From    Account_Type
        Left Outer Join Account 
          On  Account_Type.Account_Type_ID = Account.Account_Type_ID
          And Account.Region_Id = '3236f8f0-2c55-11df-8a39-0800200c9a66'
Group By Account_Type.Name

Take a look at this thread where I explain this:

http://tek-tips.com/viewthread.cfm?qid=1504081

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Sorry Rudy. Your post wasn't there when I started responding.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

that's okay george, it happens

at least you didn't give a link to some other website to explain the problem ;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[HestonJames]

Ah spot on guys!

That makes perfect sense, I knew I was close and was confused as to what was going on!

Thanks a great deal!

Heston

 

[markros]

I'm wondering why nobody wrote a blog /wiki page about this very common mistake yet?

Hmm, may be I will then - since I don't have enough blogs.

PluralSight Learning Library