I have this MySQL query that is supposed to give me the amount in and out of all bank accounts in the database for the last 30 days. It even fills gaps with the use of a join statement with a calendar table.
However I ran into issues limiting the dataset only to include the statements belonging to a specific account, defined by a account_id
field.
Here is the original query that fetches all entries in database for a specific account:
SELECT
COUNT(transactions.account_id) AS numTransactions,
calendar.datefield AS date,
SUM(CASE WHEN transactions.amount < 0 THEN transactions.amount ELSE 0 END) as negativeAmount,
SUM(CASE WHEN transactions.amount > 0 THEN transactions.amount ELSE 0 END) as positiveAmount
FROM
transactions
RIGHT JOIN
calendar
ON
DATE(transactions.accounting_date) = calendar.datefield
WHERE
calendar.datefield BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND NOW()
AND
transactions.account_id = 1
GROUP BY
date
ORDER BY
date DESC;
This will return only the days with values for transactions.amount
ignoring the RIGHT JOIN calendar
clause.
What am I missing here?
I actually solved it fairly quickly.
My WHERE
clause is on the left table, and not the right (?).
This worked:
SELECT
COUNT(transactions.account_id) AS numTransactions,
calendar.datefield AS date,
SUM(CASE WHEN transactions.amount < 0 THEN transactions.amount ELSE 0 END) as negativeAmount,
SUM(CASE WHEN transactions.amount > 0 THEN transactions.amount ELSE 0 END) as positiveAmount
FROM
transactions
RIGHT JOIN
calendar
ON
DATE(transactions.accounting_date) = calendar.datefield
AND
transactions.account_id = 1
WHERE
calendar.datefield BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND NOW()
GROUP BY
date
ORDER BY
date DESC;