View - Return 0 if no rows found in a grouped by query

Question

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.

Answer 1

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)