CODEWITHSUNDEEP
mysql
manwill
manwill

Reputation: 457

SUM transactions by account for all accounts

I'm tracking transactions in MySQL, and I'm trying to put together a report to show me the total for each account for a given month. Right now, I can get it to show me the totals for any account that has transactions, but the problem is that I need it to also return accounts without transactions, which the current query doesn't do.

My database design has two tables, one for accounts (with a name and id for each account), and a transactions table (with id, accountId, date, and amount fields). My current query looks like this:

SELECT 
    SUM(transactions.amount) AS monthly_total,
    accounts.id AS account_id,
    accounts.name AS account_name
FROM accounts
LEFT JOIN transactions ON transactions.accountId = accounts.id
WHERE transactions.date BETWEEN '2014-01-01' AND '2014-01-31'
GROUP BY accounts.id
ORDER BY accounts.name

Like I said, this query works except that it doesn't return ALL accounts. It only returns accounts for which there is a transaction recorded. Any ideas?

Upvotes: 1

Views: 916

Answers (2)

DwB
DwB

Reputation: 38320

Try this

...
sum(ifnull(transactions.amount, 0))
...
right JOIN transactions ON transactions.accountId = accounts.id
...

Upvotes: 0

Praveen Lobo
Praveen Lobo

Reputation: 7187

Your WHERE condition is filtering out the accounts that don't have a transaction.

Try - 

SELECT 
    SUM(transactions.amount) AS monthly_total,
    accounts.id AS account_id,
    accounts.name AS account_name
FROM accounts
LEFT JOIN transactions ON transactions.accountId = accounts.id 
        AND transactions.date BETWEEN '2014-01-01' AND '2014-01-31'
GROUP BY accounts.id
ORDER BY accounts.name

Upvotes: 4

Related Questions