Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mUrHc-0004s6-Oz for pgsql-sql@arkaria.postgresql.org; Mon, 27 Sep 2021 14:07:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mUrHa-00083C-R2 for pgsql-sql@arkaria.postgresql.org; Mon, 27 Sep 2021 14:07:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mUrHa-000833-FJ for pgsql-sql@lists.postgresql.org; Mon, 27 Sep 2021 14:07:02 +0000 Received: from p3plsmtpa11-02.prod.phx3.secureserver.net ([68.178.252.103]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mUrHS-0003cX-TL for pgsql-sql@lists.postgresql.org; Mon, 27 Sep 2021 14:07:02 +0000 Received: from smtpclient.apple ([152.234.153.12]) by :SMTPAUTH: with ESMTPSA id UrHLmyxXpK0kRUrHNmhBBq; Mon, 27 Sep 2021 07:06:52 -0700 X-CMAE-Analysis: v=2.4 cv=E+sIGYRl c=1 sm=1 tr=0 ts=6151cffc a=GOOB6Rrwtw3XTEX9/BZHKg==:117 a=GOOB6Rrwtw3XTEX9/BZHKg==:17 a=x7bEGLp0ZPQA:10 a=PkefZFhya44A:10 a=Yz3d8LRuAAAA:8 a=pGLkceISAAAA:8 a=epTmVMiNAAAA:8 a=uPZiAMpXAAAA:8 a=I6Qxuqm4FBIdw2bLSJIA:9 a=QEXdDO2ut3YA:10 a=Ps28cBBWURYA:10 a=eYK62Gh41NIA:10 a=QRib4286_edNUtBWNqEA:9 a=3tCql1lPxRB9cEr5:21 a=_W_S_7VecoQA:10 a=q6RDxQ7-xZ9OPdKzL-Sx:22 a=ndEWmUVY6Yapc0oHF_P4:22 X-SECURESERVER-ACCT: iuri@iurix.com From: Iuri Sampaio Message-Id: <66CD7D72-62D0-4ED1-BA98-3C8B9ED738C1@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_85DB02B5-5D15-4887-8EDB-1079E1245CC4" Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.100.0.2.22\)) Subject: Re: Creating a query to structure results grouped by two columns, pivoting only a third colum Date: Mon, 27 Sep 2021 11:06:47 -0300 In-Reply-To: Cc: pgsql-sql To: Steve Midgley References: <4A8701B1-72AA-408F-9A7D-8DDC358E8AB3@gmail.com> X-Mailer: Apple Mail (2.3654.100.0.2.22) X-CMAE-Envelope: MS4xfPwRyK0UUZEZ8eNXWSzGh6iEIM3vPAPVVcMv2nJe7eVPVBFWhAT1OL9m8TVS/9Ip0nv57Ivk7uqdrlhAyH+4M+RsRNVCp+aA/F+iXdLW+LdDjSvxV3y0 lx7GJlSCHVlBN64nBRjs1XTK79aOU9ys2h2+BzMEpBn3iY8djsr4aV6MAgbDFG1kw8NoT8bdTuOO46IW+ls6BdLM96S+VXGZiiRIYguPbRcmqXwyZ+BpvW3f 5ebnNK5mAPiE1MAfCEo1ww== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_85DB02B5-5D15-4887-8EDB-1079E1245CC4 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Using PySpark API, the solution would be=20 ```` df =3D df_table1.groupBy("area", "canal").pivot("date").sum("peso") ```` In oracle 11g, it would be sort of=20 ```` SELECT * FROM table1 PIVOT (sum(peso) FOR date IN ( '2021-03-01', '2021-04-01', '2021-05-01', '2021-06-01', '2021-07-01', = '2021-08-01', '2021-0-01=E2=80=99=20 ) P ```` Oracle has this strange way of dealing with PIVOT, without even need to = group columns.=20 If I were to write the query, ut would be somehtinglike this (i.e. = similar to Oracle but more explicit). SELECT * FROM ( SELECT=20 date,=20 area,=20 sub_canal, SUM(peso) AS peso FROM table1 GROUP BY 1, 2, 3 ORDER BY 1, 2, 3=20 ) t1=20 PIVOT ( peso FOR dat_referencia_mes IN ('2021-03-01', '2021-04-01', = '2021-05-01', '2021-06-01', '2021-07-01', '2021-08-01', '2021-0-01' ) ) p another thing that I would have done is to replace the static set of = dates to a dynamic one. (i.e. SELECT DISTINCT date FROM table1) However, I=E2=80=99m dealing with syntax errors now, plus once I fix = them I=E2=80=99m not sure if that will work ( I mean =20 > On Sep 25, 2021, at 21:53, Steve Midgley wrote: >=20 >=20 >=20 > On Sat, Sep 25, 2021 at 9:49 AM Iuri Sampaio > wrote: > [FROM]=20 > ########################### > 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 > =E2=80=A6. =E2=80=A6 = =E2=80=A6 ... > 2021-06-01 area1 can1 > 2021-06-01 area1 can2 > 2021-06-01 area1 can3 > 2021-06-01 area2 can1 > =E2=80=A6 =E2=80=A6 = ... >=20 > 2021-12-01 area1 > 2021-12-01 area1 > 2021-12-01 area1 > ########################### >=20 >=20 > [TO]=20 >=20 > ########################### > area | canal | 2021-03-01 = | 2021-04-01 | = 2021-05-01 | 2021-06-01 | ... > peso = peso = peso peso >=20 > area1 can1 45.6768 = 875.98 = 1.232 =E2=80=A6=09 > 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) ... >=20 > area2 can1 1.87 = =E2=80=A6 = ... > area2 can2 12.7687 > area2 can3 965.568 > area2 total = SUM(1.87+12.7687+965.568) =E2=80=A6 = ... >=20 >=20 > area3 can1 968.95 > area3 can2 1.6867 > area3 can3 8.897 >=20 > ... > ########################### >=20 >=20 > I think you'd want to use the "crosstab" function in the module = "tablefunc" https://www.postgresql.org/docs/current/tablefunc.html = >=20 > This post goes into quite a bit of detail on how to implement this = type of query: = https://stackoverflow.com/questions/3002499/postgresql-crosstab-query/1175= 1905#11751905 = >=20 > Does that get you close enough? > Steve=20 >=20 > On Sat, Sep 25, 2021 at 9:49 AM Iuri Sampaio > wrote: > 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: >=20 > ```` > SELECT=20 > t1.date,=20 > t1.area,=20 > t1.canal, > SUM(t1.peso) AS peso > FROM table1 t1 > GROUP BY 1, 2, 3 > ORDER BY 1, 2, 3=20 >=20 > ```` >=20 > and source query generates a initial structure as in: >=20 > ########################### > 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 > =E2=80=A6. =E2=80=A6 = =E2=80=A6 ... > 2021-06-01 area1 can1 > 2021-06-01 area1 can2 > 2021-06-01 area1 can3 > 2021-06-01 area2 can1 > =E2=80=A6 =E2=80=A6 = ... >=20 > 2021-12-01 area1 > 2021-12-01 area1 > 2021-12-01 area1 > ########################### >=20 >=20 >=20 >=20 > Then, the goal's to achieve a final structure grouped by columns = "area" and "canal", pivoting column =E2=80=9Cdate", but only to the = column "peso". > Plus, a partial total of each area, named as "total=E2=80=9D . >=20 > I tried to write a query to support such a display, however, I got = stuck at pivoting date only to the column peso >=20 > ```` > SELECT =20 > t2.area, > t2.canal, > ( SELECT=20 > month, > peso_valor > FROM ( > SELECT=20 > month(t1.date) month,=20 > t1.area,=20 > 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=20 > ) PIVOT ( =20 > 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 > ) =20 > ) as pivot_table > ORDER BY month > ) as t2.peso=20 > FROM tbl_data t2 > GROUP BY 1, 2 > ```` >=20 >=20 > what would be the SQL implementation to achieve a structure as such ? = =20 > (.i.e. grouped by columns "area" and "canal", pivoting column = =E2=80=9Cdate", but only to the column "peso=E2=80=9D.) >=20 > illustrated bellow? >=20 >=20 > Best wishes, > I >=20 >=20 >=20 >=20 >=20 >=20 >=20 >=20 > ########################### > area | canal | 2021-03-01 = | 2021-04-01 | = 2021-05-01 | 2021-06-01 | ... > peso = peso = peso peso >=20 > area1 can1 45.6768 = 875.98 = 1.232 =E2=80=A6=09 > 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) ... >=20 > area2 can1 1.87 = =E2=80=A6 = ... > area2 can2 12.7687 > area2 can3 965.568 > area2 total = SUM(1.87+12.7687+965.568) =E2=80=A6 = ... >=20 >=20 > area3 can1 968.95 > area3 can2 1.6867 > area3 can3 8.897 >=20 > ... > ########################### >=20 >=20 --Apple-Mail=_85DB02B5-5D15-4887-8EDB-1079E1245CC4 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Using= PySpark API, the solution would be 

````
df =3D = df_table1.groupBy("area", "canal").pivot("date").sum("peso")
````

In oracle 11g,  it would be sort = of 

````
SELECT * FROM table1 PIVOT = (sum(peso) FOR date IN (
   '2021-03-01', = '2021-04-01', '2021-05-01', '2021-06-01', '2021-07-01', '2021-08-01', = '2021-0-01=E2=80=99 
 ) P
````

Oracle has this strange way of dealing with PIVOT, without = even need to group columns. 


If = I were to write the query, ut would be somehtinglike this (i.e. similar = to Oracle but more explicit).

SELECT * FROM = (
  SELECT 
  =   date, 
    = area, 
    sub_canal,
    SUM(peso) AS peso
  = FROM table1
  GROUP BY 1, 2, 3
  ORDER BY 1, 2, 3 
) = t1 
PIVOT (
  peso = FOR dat_referencia_mes IN ('2021-03-01', '2021-04-01', '2021-05-01', = '2021-06-01', '2021-07-01', '2021-08-01', '2021-0-01' )
) p



another thing that I would have done is to replace the static = set of dates to a dynamic one. (i.e. SELECT DISTINCT date FROM = table1)



However, I=E2=80=99m = dealing with syntax errors now, plus once  I fix them I=E2=80=99m = not sure if that will work ( I mean  



On Sep 25, 2021, at 21:53, = Steve Midgley <science@misuse.org> wrote:



On Sat, Sep 25, 2021 at 9:49 AM Iuri = Sampaio <iuri.sampaio@gmail.com> wrote:
 [FROM] 
###########################
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
=E2=80=A6. =E2=80=A6 = =E2=80=A6 = ...
2021-06-01 = area1 = can1
2021-06-01 = area1 = can2
2021-06-01 = area1 = can3
2021-06-01 = area2 = can1
=E2=80=A6 = =E2=80=A6 ...

2021-12-01 = area1
2021-12-01 = area1
2021-12-01 = area1
###########################


[TO] 

###########################
= 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 = =E2=80=A6
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 = =E2=80=A6 = ...
area2 = can2 = 12.7687
area2 = can3 = 965.568
= area2 = total = SUM(1.87+12.7687+965.568) = =E2=80=A6 = ...


area3 = can1 = 968.95
area3 = can2 = 1.6867
area3 = can3 = 8.897

...
###########################


I think you'd want to use the "crosstab" function in = the module "tablefunc" https://www.postgresql.org/docs/current/tablefunc.html

This post goes into = quite a bit of detail on how to implement this type of query: https://stackoverflow.com/questions/3002499/postgresql-crosstab= -query/11751905#11751905

Does that get you close = enough?
Steve 

On Sat, Sep 25, 2021 at 9:49 AM Iuri Sampaio <iuri.sampaio@gmail.com> wrote:
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
=E2=80=A6. =E2=80=A6 = =E2=80=A6 = ...
2021-06-01 = area1 = can1
2021-06-01 = area1 = can2
2021-06-01 = area1 = can3
2021-06-01 = area2 = can1
=E2=80=A6 = =E2=80=A6 ...

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 =E2=80=9Cdate", but only to the column "peso".
Plus, a partial total = of each area, named as "total=E2=80=9D .

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 =E2=80=9Cdate", but only to the column = "peso=E2=80=9D.)

 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 = =E2=80=A6
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 = =E2=80=A6 = ...
area2 = can2 = 12.7687
area2 = can3 = 965.568
= area2 = total = SUM(1.87+12.7687+965.568) = =E2=80=A6 = ...


area3 = can1 = 968.95
area3 = can2 = 1.6867
area3 = can3 = 8.897

...
###########################



= --Apple-Mail=_85DB02B5-5D15-4887-8EDB-1079E1245CC4--