all rows from left table with sum of amount from right table 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 &gt;='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.
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 &gt;='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.