agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Iuri Sampaio <iuri.sampaio@gmail.com>
To: pgsql-sql@lists.postgresql.org
Subject: Creating a query to structure results grouped by two columns, pivoting only a third colum
Date: Sat, 25 Sep 2021 13:39:34 -0300
Message-ID: <4A8701B1-72AA-408F-9A7D-8DDC358E8AB3@gmail.com> (raw)
Hello there,
In order to achieve such structure "pivoting" table and "grouping by" multiple columns. (i.e. as illustrated below), what would be the SQL implementation?
The source query is:
````
SELECT
t1.date,
t1.area,
t1.canal,
SUM(t1.peso) AS peso
FROM table1 t1
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3
````
and source query generates a initial structure as in:
###########################
date | area | canal | peso
2021-03-01 area1 can1 45.6768
2021-03-01 area1 can2 54.6768
2021-03-01 area1 can3 87.6
2021-03-01 area2 can1 1.87
2021-03-01 area2 can2 12.7687
2021-03-01 area2 can3 965.568
2021-03-01 area3 can1 968.95
2021-03-01 area3 can2 1.6867
2021-03-01 area3 can3 8.897
…. … … ...
2021-06-01 area1 can1
2021-06-01 area1 can2
2021-06-01 area1 can3
2021-06-01 area2 can1
… … ...
2021-12-01 area1
2021-12-01 area1
2021-12-01 area1
###########################
Then, the goal's to achieve a final structure grouped by columns "area" and "canal", pivoting column “date", but only to the column "peso".
Plus, a partial total of each area, named as "total” .
I tried to write a query to support such a display, however, I got stuck at pivoting date only to the column peso
````
SELECT
t2.area,
t2.canal,
( SELECT
month,
peso_valor
FROM (
SELECT
month(t1.date) month,
t1.area,
t1.canal,
SUM(t1.peso_valor) AS peso_valor
FROM tbl_data t1
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3
) source_table
) PIVOT (
peso
FOR month IN (
1 Jan, 2 Feb, 3 Mar, 4 Apr, 5 Mai, 6 Jun, 7 Jul, 8 Aug, 9 Sep, 10 Oct, 11 Nov, 12 Dec
)
) as pivot_table
ORDER BY month
) as t2.peso
FROM tbl_data t2
GROUP BY 1, 2
````
what would be the SQL implementation to achieve a structure as such ?
(.i.e. grouped by columns "area" and "canal", pivoting column “date", but only to the column "peso”.)
illustrated bellow?
Best wishes,
I
###########################
area | canal | 2021-03-01 | 2021-04-01 | 2021-05-01 | 2021-06-01 | ...
peso peso peso peso
area1 can1 45.6768 875.98 1.232 …
area1 can2 54.6768 665.8 2.43 ...
area1 can3 87.6 65.8 4.76 ...
area1 total SUM(45.6768+54.6768+87.6) SUM(875.98+665.8+65.8) SUM(1.232+2.43+4.76) ...
area2 can1 1.87 … ...
area2 can2 12.7687
area2 can3 965.568
area2 total SUM(1.87+12.7687+965.568) … ...
area3 can1 968.95
area3 can2 1.6867
area3 can3 8.897
...
###########################
view thread (3+ messages) latest in thread
Message-ID: <4A8701B1-72AA-408F-9A7D-8DDC358E8AB3@gmail.com>
Permalink: ../4A8701B1-72AA-408F-9A7D-8DDC358E8AB3@gmail.com/
Also on: postgresql.org/message-id/4A8701B1-72AA-408F-9A7D-8DDC358E8AB3@gmail.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: iuri.sampaio@gmail.com, pgsql-sql@lists.postgresql.org
Subject: Re: Creating a query to structure results grouped by two columns, pivoting only a third colum
In-Reply-To: <4A8701B1-72AA-408F-9A7D-8DDC358E8AB3@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox