0
Good night. I need to do a sales report grouped by period and by card. But I do not know how to enter a store and horizontally of it appear the values of each card. Example of how the output should be: Loja01, with 03 sales(100 visa; 55 visa; 200 master) should appear in the report as follows:
Follow my current code in SQL Fiddle: http://sqlfiddle.com/#! 17/26e74/1
OBS: BD Postgresql
0
The use of the extension is common tablefunc with the function crosstab to generate the results in this way. Another alternative is to use the json functions of postgres to generate the results.
Json can be generated directly by postgres and then using the resources of the frontend programming language to make the results available in column:
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 the 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 function json_object_agg is available since version 9.4 of postgres.
http://bender.io/2016/09/18/dynamic-pivot-tables-with-json-and-postgresql/ https://www.postgresql.org/docs/9.4/static/functions-aggregate.html https://www.postgresql.org/docs/current/static/tablefunc.html
In parts it worked, the problem is that it doesn’t add up the whole line. To give a total result of lines. I never used this json_object_agg function, as I would create a column to add the lines?
And another question, if this is not a web application, the json_object_agg function will work normally?
I edited the answer by adding a way to return the total of each store. The function json_object_agg is a type of postgres data, so it will work independently of the language. I mentioned about returning json and working on the frontend, or other kind of programming language, because it’s probably simpler to manipulate the data. But you can use the last code that returns in the traditional format.
So, I understood. It was pretty cool. Another question how I work the null fields. For example if one Loja02 did not sell on the card Master and Diners. How do I assign zero instead of null? Is there a Where clause for this? Or some other procedure?
I believe the function coalesce resolve. I’m glad I helped, don’t forget to punctuate the answer :)
Browser other questions tagged database postgresql query select-sql
You are not signed in. Login or sign up in order to post.
Do you want to return one line for each store? You could edit the question and give a formatted one in the example, maybe an image with the example of the result in the layout similar to a table would be easier for the community people to understand your problem.
– Camilo Santos
I added the photo of how the exit needs to be. I was searching and saw that this is a dynamic table ( I had never done one), and I will need to use Crosstab, but it’s not going.
– Lucas Esbegue