Hello, I need to know how best to use COALESCE to handle the null fields in my table.
My problem is the following, I'm simulating 6 Stores and their respective cards, let's suppose that one or more stores did not sell product in the Amex card, when I execute my query it returns me 'null'. And I needed to replace it with zero.
With COALESCE, is it possible?
Here's a photo: 
Query Image: 
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 must first modify the fields by performing an explicit conversion to numeric and entering the second parameter 0 when there is no flag value.
coalesce(cast(vb.bandeiras->>'AMEX' as numeric), 0) as amex
Follow the complete query below. I created a new ELO column that does not have records in the tables to simulate the handling 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] link