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.

Aiias · Accepted Answer · 2013-03-24T03:49:18.490

9

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)

edited Mar 24 '13 at 03:49

answered Mar 24 '13 at 03:42

Aiias

4,683
1
18
34

that's weird: `Empty set (0,01 sec)` – caarlos0 Mar 24 '13 at 03:46
@caarlos0 - See my edit. This should work when you get results and when you don't. But it's not the most elegant. – Aiias Mar 24 '13 at 03:49

Ivan Ivanov · Answer 2 · 2017-07-05T08:28:53.027

0

For positive values I use

select max(x.cnt) as cnt
from (
select ifnull(meta_value, 0) as cnt from wp_postmeta where post_id = 5543 and meta_key = '_bbp_voice_count'
union
select 0 as cnt) x

or for any

select x.cnt 
from (
select ifnull(meta_value, 0) as cnt from wp_postmeta where post_id = 5543 and meta_key = '_bbp_voice_count'
union
select 0 as cnt
) x
limit 1

edited Jul 05 '17 at 08:28

answered Jul 05 '17 at 08:20

Ivan Ivanov

2,076
16
33

score -1 · Answer 3 · edited May 23 '17 at 12:22

-1

In production, don't use SELECT *. See this SO question for a thorough response as to why.

So, assuming you're not, you can use COALESCE, which will return the first non-null value.

SELECT 
    COALESCE(total, 0) 
FROM
    total_transactions
WHERE
    account_id = '2600'

edited May 23 '17 at 12:22

Community

1
1

answered Mar 24 '13 at 03:43

Luke Shaheen

4,262
12
52
82

I already tried it: `select COALESCE(total, 0) as val from total_transactions where account_id = 2060; Empty set (0,01 sec)` – caarlos0 Mar 24 '13 at 03:45
Is `total` a field name in the `total_transactions` table or is it an alias for a calculated result? – Luke Shaheen Mar 24 '13 at 03:48
7

This will not work because there are no rows, so there is nothing to coalesce. – BadHorsie Jun 06 '17 at 18:52

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

EDIT

3 Answers3