0

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.

3
  • Even when there is no ambiguity, it is good practice to qualify the attributes with aliases. I assume i.firstname, i.lastname
    – Lennart
    Aug 7 '15 at 11:06
  • 1
    Ok. Edited the question. Usually my queries are just for me, so I allow myself some laziness :)
    – MrVimes
    Aug 7 '15 at 11:09
  • sum(amount) -> sum(i.amount) ;) Aug 7 '15 at 11:57
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.

4
  • 1
    Good, just make sure to mark it as an answer.
    – Lennart
    Aug 7 '15 at 11:08
  • 1
    I will, but I can't for two days.
    – MrVimes
    Aug 7 '15 at 11:09
  • and you probably need isnull(sum(amount),0) Aug 7 '15 at 11:59
  • It works either way, or with just sum(amount)
    – MrVimes
    Aug 7 '15 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.