Good evening. I need to do a sales report grouped by period and by card. But I do not know how I can enter a store and horizontally it appears the values of each card. Example of what the output should look like: Loja01, with 03 sales (100 visa, 55 visa, 200 master) should appear in the report as follows:
FollowmycurrentcodeinSQLFiddle: link
NOTE: BD PostgreSQL
It is common to use the tablefunc extension with the crosstab function to generate the results in this way. Another alternative is to use the json functions of postgres to generate the results.
You can generate json directly through postgres and then use the features of the frontend programming language to make column results available:
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
)
select
store_nome,
json_object_agg(card_nome,total_sale) as bandeiras
from vendas
group by store_nome
store_nome | bandeiras
------------+--------------------------------------------------------------
Loja 02 | { "AMEX" : 60, "DINERS" : 60, "MASTER" : 60, "VISA" : 60 }
Loja 01 | { "AMEX" : 150, "DINERS" : 80, "MASTER" : 50, "VISA" : 100 }
(2 registros)
Data can also be returned by postgres already with values separated by column using the -> operator:
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,
vb.bandeiras->'AMEX' as amex,
vb.bandeiras->'VISA' as visa,
vb.bandeiras->'DINERS' as diners,
vb.bandeiras->'MASTER' as master,
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 | total
------------+------+------+--------+--------+-------
Loja 02 | 60 | 60 | 60 | 60 | 240
Loja 01 | 150 | 100 | 80 | 50 | 380
(2 registros)
The json_object_agg function is available since postgres 9.4.