View - Return 0 if no rows found in a grouped by query
mysql, view
Solution
If there is not an entry in the view results, then this will always return `NULL` - That's SQL. If you change your `SELECT` that you use against the view, you can achieve what you want:
SELECT IFNULL(total, 0) FROM total_transactions WHERE account_id = 2060
Edit:
(SELECT IFNULL(total, 0) total FROM total_transactions WHERE account_id = 2060)
UNION
(SELECT 0 total)
Problem
Let's say I have the following MySQL view: ``` create or replace view total_transactions(account_id, total) as select t.account_id, ifnull(sum(t.value), 0) as total from transactions t where t.paid IS TRUE group by t.bank_account_id; ``` Let's say the account doesn't have any transaction yet, I want the view to return 0. Right now, if I do a select like: ``` select * from total_transactions where account_id = 2060; ``` And account 2060 didn't had any transaction, it will return me nothing, instead of 0. How could I fix that? Thanks in advance. EDIT I think it could be something with the `group by`... If I execute the query that I'm using for the view without the group by, it works (return 0 even with no results), but if I use `group by` it comes null: ``` select t.account_id, ifnull(sum(t.value), 0) as total from transactions t where t.paid IS TRUE and account_id = 2060; ``` Returns `0`, and ``` create or replace view total_transactions(account_id, total) as select t.account_id, ifnull(sum(t.value), 0) as total from transactions t where t.paid IS TRUE and account_id = 2060 group by t.bank_account_id; ``` Return an empty set.