all rows from left table with sum of amount from right table

Question

I'm trying to return a list of customers and the sum of their transactions in the last week, but have the query return customers who didn't wager at all. I am trying to do this with a left join, but the query only returns a row if a customer did wager, which is not how I expect the left join to work. What can I do to make this work the way I want it?

select i.accountnumber, i.firstname, i.lastname, sum(a.amount)
from accountinformaton i 
left join accountactivity a 
 on i.accountnumber = a.accountnumber
where a.transactiontype = 'Bet' 
 and a.transactiondate >='2015-07-31'
group by i.accountnumber, i.firstname, i.lastname

the 'accountinformation' table has thousands of rows, the query above returns a few hundred. I was expecting to be able to return all rows from 'accountinformation' and have the sum be null on accounts that didn't wager.

• Lennart - Slava Ukraini: Even when there is no ambiguity, it is good practice to qualify the attributes with aliases. I assume i.firstname, i.lastname
• MrVimes: Ok. Edited the question. Usually my queries are just for me, so I allow myself some laziness :)
• ypercubeᵀᴹ: sum(amount) -> sum(i.amount) ;)

Answer 1

Figured it out myself. My where clause is telling sql to only show rows that are 'Bet' and have that date criteria, so without me realising it I'm telling sql to show me rows that had bets.

Here is my alternative query...

select i.accountnumber, i.firstname, i.lastname, sum(isnull(amount,0))
from accountinformation i 
left join accountactivity a 
 on i.accountnumber = a.accountnumber 
 and a.transactiondate >='2015-07-31' 
 and a.transactiontype = 'Bet'
group by i.accountnumber, i.firstname, i.lastname

Putting the conditions in the join clause instead of the where clause makes the left join behave as expected.

• Lennart - Slava Ukraini: Good, just make sure to mark it as an answer.
• MrVimes: I will, but I can't for two days.
• ypercubeᵀᴹ: and you probably need isnull(sum(amount),0)
• MrVimes: It works either way, or with just sum(amount)