The coalesce
is a function that returns the value of the first parameter if it is not null. If null returns the value entered in the second parameter, according to documentation [1].
In the case of the informed query you first need to modify the fields by performing an explicit conversion to numeric
and informing in the second parameter 0
where there is no flag value.
coalesce(cast(vb.bandeiras->>'AMEX' as numeric), 0) as amex
Follow full query below. I created a new column ELO
that has no records in the tables to simulate the treatment of null values.
with vendas as(
select
t.nome as store_nome,
c.nome as card_nome,
sum(s.valor) as total_sale
from sale s
inner join store t
on (t.id = s.store_id_fk)
inner join credit_card c
on (c.id = s.credit_card_fk)
where
data between '2017-01-01' and '2017-01-31'
group by
t.nome,
c.nome
), vendas_por_loja as (
select
store_nome,
sum(total_sale) as total
from vendas
group by store_nome
), vendas_por_bandeira as (
select
store_nome,
jsonb_object_agg(card_nome,total_sale) as bandeiras
from
vendas
group by store_nome
)
select
vb.store_nome,
coalesce(cast(vb.bandeiras->>'AMEX' as numeric), 0) as amex,
coalesce(cast(vb.bandeiras->>'VISA' as numeric), 0)as visa,
coalesce(cast(vb.bandeiras->>'DINERS' as numeric), 0)as diners,
coalesce(cast(vb.bandeiras->>'MASTER' as numeric), 0) as master,
coalesce(cast(vb.bandeiras->>'ELO' as numeric), 0) as elo,
vl.total
from vendas_por_bandeira vb
inner join vendas_por_loja vl
on (vb.store_nome=vl.store_nome);
store_nome | amex | visa | diners | master | elo | total
------------+------+------+--------+--------+-----+-------
Loja 02 | 60 | 60 | 60 | 60 | 0 | 240
Loja 01 | 150 | 100 | 80 | 50 | 0 | 380
(2 registros)
[1] https://www.postgresql.org/docs/current/static/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL
I believe it has already been answered, but in a next question, do not use images to show the code, put as even formatted text. Images you can use to show the result, or a screen for example.
– Rovann Linhalis