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 1kR55V-0004tG-7k for pgsql-sql@arkaria.postgresql.org; Sat, 10 Oct 2020 02:58:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kR55T-0000U4-QG for pgsql-sql@arkaria.postgresql.org; Sat, 10 Oct 2020 02:58:23 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kR55T-0000Tw-Az for pgsql-sql@lists.postgresql.org; Sat, 10 Oct 2020 02:58:23 +0000 Received: from p3plsmtpa08-04.prod.phx3.secureserver.net ([173.201.193.105]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kR55Q-0005hf-Th for pgsql-sql@postgresql.org; Sat, 10 Oct 2020 02:58:22 +0000 Received: from [192.168.1.3] ([186.241.25.164]) by :SMTPAUTH: with ESMTPSA id R55LknAUjWBUMR55MkguAn; Fri, 09 Oct 2020 19:58:18 -0700 X-CMAE-Analysis: v=2.3 cv=YcjDGDZf c=1 sm=1 tr=0 a=nbPSJA7ApY4wULB72PBkzw==:117 a=nbPSJA7ApY4wULB72PBkzw==:17 a=x7bEGLp0ZPQA:10 a=PkefZFhya44A:10 a=epTmVMiNAAAA:8 a=GJX-zPN8zO-vkyiq0cYA:9 a=QEXdDO2ut3YA:10 a=j-2t6rTZjwkA:10 a=n9qCkoiI08IA:10 a=jE5RGApOpsvMpMaW0FkA:9 a=HlwOua6ynGFLbyLG:21 a=_W_S_7VecoQA:10 a=ndEWmUVY6Yapc0oHF_P4:22 X-SECURESERVER-ACCT: iuri@iurix.com From: Iuri Sampaio Content-Type: multipart/alternative; boundary="Apple-Mail=_B0A81649-8075-439E-9688-B5B8A03EC3AC" Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.1\)) Subject: total and partial sums in the same query?? Message-Id: <88EE8FAD-AE7D-42A6-9DC1-43B2442960A4@gmail.com> Date: Fri, 9 Oct 2020 23:58:15 -0300 To: pgsql-sql@postgresql.org X-Mailer: Apple Mail (2.3608.120.23.2.1) X-CMAE-Envelope: MS4wfM/QJVLpwL8BF0/mxz2gzvoyoCuhokTCBia6RSqahaXtI8WnE5YRQUIh373I9nUSTWWE+8kKz+vyyFKX0E2Xz54hZRNaqtPVSrdsu0TDvsqSuuwPHG+d TrU4HJjVugGZganf/wEpXu0u0QXg3uHH7UafmSZL4gNRo/WcoC+RZKLhA32IxKzGxEk7RP/bniGnPQ== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_B0A81649-8075-439E-9688-B5B8A03EC3AC Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Is there a way to return total and partial sums (grouped by a third = column) in the same query? =20 Total is an aggregate function i.e. COUNT(1), partial is some sort of = conditional as in: CASE WHEN EXTRACT(MONTH FROM date) =3D 10 THEN = COUNT(1) , =E2=80=A6. I've tried to Window functions = https://www.postgresql.org/docs/9.1/tutorial-window.html = however, it = was not possible to recognize the partition SELECT split_part(description, ' ', 25) AS type, COUNT(1), COUNT(1) OVER = (PARTITION split_part(description, ' ', 25) WHERE EXTRACT(MONTH FROM = creation_date::date) =3D 10 AS TotalOctober FROM qt_vehicle_ti GROUP BY = type; ); ERROR: syntax error at or near "split_part" LINE 1: ... 25) AS type, COUNT(1), COUNT(1) OVER (PARTITION = split_part... The column =E2=80=9Cdescription" is manipulated with split_part to allow = GROUP BY to sort and count by categories, which is one word among others = within the description column, as in . {id 7281 plate_number FRP380 first_seen {2020-07-15 14:50:26} last_seen = {2020-07-15 14:50:26} probability 0.6 location_name Test camera_name = LPR4 direction LEAVING class Car} So, the result must be something like the result bellow SELECT split_part(description, ' ', 25) AS type,=20 COUNT(1) AS total, =20 (=20 SELECT COUNT(1) as partial FROM qt_vehicle_ti v2 WHERE = split_part(v2.description, ' ', 25) =3D split_part(description, ' ', 25) = AND EXTRACT(MONTH FROM v2.creation_date::date) =3D 10 ) AS partial=20 FROM qt_vehicle_ti GROUP BY type; type | count | partial=20 ------------+--------+-------------- Bus | 6702 | 8779 Car | 191761 | 8779 Motorbike | 3746 | 8779 SUV/Pickup | 22536 | 8779 Truck | 21801 | 8779 Unknown | 588341 | 8779 Van | 7951 | 8779 Best wishes, I --Apple-Mail=_B0A81649-8075-439E-9688-B5B8A03EC3AC Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
Is there a way to = return total and partial sums (grouped by a third column) in the same = query?  

Total is an aggregate function i.e. COUNT(1),  partial = is some sort of conditional as in: CASE  WHEN EXTRACT(MONTH FROM = date) =3D 10 THEN COUNT(1) , =E2=80=A6.

I've tried to Window functions https://www.postgresql.org/docs/9.1/tutorial-window.html&nb= sp;however, it was not possible to recognize the partition


SELECT = split_part(description, ' ', 25) AS type, COUNT(1), COUNT(1) = OVER (PARTITION split_part(description, ' ', 25) WHERE = EXTRACT(MONTH FROM creation_date::date) =3D 10 AS TotalOctober FROM = qt_vehicle_ti GROUP BY type;
);
ERROR:  syntax error at or near = "split_part"
LINE 1: ... 25) AS type, COUNT(1), COUNT(1) OVER  = (PARTITION split_part...



The column =E2=80=9Cdescripti= on" is manipulated with split_part to allow GROUP BY to sort and count = by categories, which is one word among others within the = description column, as in .

{id = 7281 plate_number FRP380 first_seen {2020-07-15 14:50:26} last_seen = {2020-07-15 14:50:26} probability 0.6 location_name Test camera_name = LPR4 direction LEAVING class Car}


So, the result must be something like the result = bellow


SELECT = split_part(description, ' ', 25) AS type, 
COUNT(1) AS total, =   
( 
  =    SELECT COUNT(1) as partial FROM qt_vehicle_ti v2 WHERE = split_part(v2.description, ' ', 25) =3D split_part(description, ' ', 25) = AND EXTRACT(MONTH FROM v2.creation_date::date) =3D 10
) AS = partial 
FROM = qt_vehicle_ti GROUP BY type;




  =  type    | count  | partial 
------------+--------+--------------
Bus       =  |   6702 |         8779
Car =        | 191761 |         = 8779

Motorbike  |   3746 |       =   8779
SUV/Pickup |  22536 |         = 8779

Truck      |  21801 |     =     8779

Unknown    | 588341 |       =   8779

Van        |   7951 |   =       8779


Best wishes,
I
= --Apple-Mail=_B0A81649-8075-439E-9688-B5B8A03EC3AC--