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.