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 1kR5mi-0006Y4-Fz for pgsql-sql@arkaria.postgresql.org; Sat, 10 Oct 2020 03:43:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kR5mh-0004Kf-Ey for pgsql-sql@arkaria.postgresql.org; Sat, 10 Oct 2020 03:43:03 +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 1kR5mh-0004KX-5i for pgsql-sql@lists.postgresql.org; Sat, 10 Oct 2020 03:43:03 +0000 Received: from p3plsmtpa12-09.prod.phx3.secureserver.net ([68.178.252.238]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kR5md-0007ef-Us for pgsql-sql@postgresql.org; Sat, 10 Oct 2020 03:43:02 +0000 Received: from [192.168.1.3] ([186.241.25.164]) by :SMTPAUTH: with ESMTPSA id R5mXkWQZ6sQAgR5mZkSpKs; Fri, 09 Oct 2020 20:42:56 -0700 X-CMAE-Analysis: v=2.3 cv=A9cSwJeG c=1 sm=1 tr=0 a=nbPSJA7ApY4wULB72PBkzw==:117 a=nbPSJA7ApY4wULB72PBkzw==:17 a=x7bEGLp0ZPQA:10 a=PkefZFhya44A:10 a=pGLkceISAAAA:8 a=epTmVMiNAAAA:8 a=MiqESbzreynX-H14kEgA:9 a=QEXdDO2ut3YA:10 a=j-2t6rTZjwkA:10 a=n9qCkoiI08IA:10 a=-FgyslCyBO-zvKyNqlMA:9 a=4_eXfyjScpjVSKX6:21 a=_W_S_7VecoQA:10 a=ndEWmUVY6Yapc0oHF_P4:22 X-SECURESERVER-ACCT: iuri@iurix.com From: Iuri Sampaio Message-Id: <4D4046E1-6DC2-4A36-A469-0BDBC0F86406@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_90EE7D6D-6E77-4A34-8807-5724B2900FC4" Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.1\)) Subject: Re: total and partial sums in the same query?? Date: Sat, 10 Oct 2020 00:42:53 -0300 In-Reply-To: Cc: pgsql-sql To: "David G. Johnston" References: <88EE8FAD-AE7D-42A6-9DC1-43B2442960A4@gmail.com> X-Mailer: Apple Mail (2.3608.120.23.2.1) X-CMAE-Envelope: MS4wfIQq09+tVM4ntt2vel0coWzkNcSOzQH50kBB42d3uWfyG1PxfMzSj2WiPwRrP3GwUS/Ht7Oy3OPJptjCK3z9eoghTq7nEPKZK4kBuM5hBRoED1gplk52 IHcfuVg4Aw0lHxShTPqqjhZStArJpjTVcrRytFCneUdkZjVAxL6+/hlQKqKHGCeIA78ClxFZc6hCYhurUDu1Xh6aOT8oTFNHDVAHIhry/sBUsoeZUkUcLSEY List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_90EE7D6D-6E77-4A34-8807-5724B2900FC4 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Hi David,=20 RIGHT OUTER JOIN is the key! TOTAL SELECT split_part(description, ' ', 25) AS type, COUNT(1) AS total FROM = qt_vehicle_ti GROUP BY type OCTOBER SELECT split_part(description, ' ', 25) AS type, COUNT(1) AS total FROM = qt_vehicle_ti WHERE EXTRACT(MONTH FROM creation_date) =3D 10 GROUP BY = type FINAL=20 SELECT split_part(v.description, ' ', 25) AS type, t.partial, COUNT(1) = AS total FROM qt_vehicle_ti v RIGHT OUTER JOIN ( SELECT = split_part(description, ' ', 25) AS type1, COUNT(1) AS partial FROM = qt_vehicle_ti WHERE EXTRACT(MONTH FROM creation_date) =3D 10 GROUP BY = type1) AS t ON t.type1 =3D split_part(v.description, ' ', 25) GROUP BY = type, partial =20 Let me know if you would use a different approach p.s. It=E2=80=99s very self rewarding when you ask a question and you = yourself answer it! Best wishes, I > On Saf. 23, 1442 AH, at 00:40, David G. Johnston = wrote: >=20 > On Fri, Oct 9, 2020 at 7:58 PM Iuri Sampaio > wrote: > Is there a way to return total and partial sums (grouped by a third = column) in the same query? =20 >=20 > Yes. >=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. >=20 > 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 >=20 > You should observe the version numbers when viewing documentation and = try and use either the most current docs or the version you are coding = against. If you are indeed coding against 9.1 be advised it is = considerably out-of-date. >=20 >=20 > 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... >=20 >=20 > You forget the keyword "BY" in "PARTITION BY". That explains the = immediate syntax error message. You would get many more errors due to = having made up the entire contents of the PARTITION BY portion of the = window definition (partitions are not specified using a full select-like = statement, the trailing semicolon in there is also a problem). You need = to consult the SQL Command reference documentation, in this case SELECT, = to get the full syntax for stuff - tutorials are not necessarily = comprehensive. >=20 >=20 > 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 . >=20 > {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} >=20 > This detail seems immaterial to the immediate question at hand. A = self-contained problem (see WITH/CTE) with fewer complex expressions = generally makes learning, and asking for help, easier. > =20 > So, the result must be something like the result bellow >=20 > 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; >=20 >=20 >=20 >=20 > type | count | partial=20 > ------------+--------+-------------- > Bus | 6702 | 8779 > Car | 191761 | 8779 >=20 > Motorbike | 3746 | 8779 > SUV/Pickup | 22536 | 8779 >=20 > Truck | 21801 | 8779 >=20 > Unknown | 588341 | 8779 >=20 > Van | 7951 | 8779 >=20 >=20 >=20 > What about the above example, assuming it is indeed something that = works, is wrong? > Between subqueries, window functions, and group by you've got the = tools pretty well identified. If you want help putting them together = you should construct a simplified self-contained example and, using the = provided input data, describe exactly what the output table needs to = show. >=20 > David J. --Apple-Mail=_90EE7D6D-6E77-4A34-8807-5724B2900FC4 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Hi = David, 

RIGHT OUTER JOIN is the key!

TOTAL
SELECT split_part(description, ' ', 25) AS type, COUNT(1) AS = total FROM qt_vehicle_ti GROUP BY type

OCTOBER
SELECT = split_part(description, ' ', 25) AS type, COUNT(1) AS total FROM = qt_vehicle_ti WHERE EXTRACT(MONTH FROM creation_date) =3D 10 GROUP BY = type


FINAL 
SELECT = split_part(v.description, ' ', 25) AS type, t.partial, COUNT(1) AS total = FROM qt_vehicle_ti v RIGHT OUTER JOIN ( SELECT split_part(description, ' = ', 25) AS type1, COUNT(1) AS partial FROM qt_vehicle_ti WHERE = EXTRACT(MONTH FROM creation_date) =3D 10 GROUP BY type1) AS t ON t.type1 = =3D split_part(v.description, ' ', 25) GROUP BY type, partial
 


Let me know if you would = use a different approach




p.s. It=E2=80=99s very = self rewarding when you ask a question and you  yourself answer = it!

Best = wishes,
I


On Saf. 23, 1442 AH, at 00:40, David G. = Johnston <david.g.johnston@gmail.com> wrote:

On Fri, Oct 9, 2020 at 7:58 PM Iuri = Sampaio <iuri.sampaio@gmail.com> wrote:
Is there a way to return total and partial sums (grouped by a = third column) in the same query?  

Yes.

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

You should observe = the version numbers when viewing documentation and try and use either = the most current docs or the version you are coding against.  If = you are indeed coding against 9.1 be advised it is considerably = out-of-date.


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...


You forget the = keyword "BY" in "PARTITION BY".  That explains the immediate syntax = error message.  You would get many more errors due to having made = up the entire contents of the PARTITION BY portion of the window = definition (partitions are not specified using a full select-like = statement, the trailing semicolon in there is also a problem).  You = need to consult the SQL Command reference documentation, in this case = SELECT, to get the full syntax for stuff - tutorials are not necessarily = comprehensive.


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}

This detail seems = immaterial to the immediate question at hand.  A self-contained = problem (see WITH/CTE) with fewer complex expressions generally makes = learning, and asking for help, easier.
 
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



What about the = above example, assuming it is indeed something that works, is = wrong?
Between subqueries, window functions, and group by you've = got the tools pretty well identified.  If you want help putting = them together you should construct a simplified self-contained example = and, using the provided input data, describe exactly what the output = table needs to show.

David = J.

= --Apple-Mail=_90EE7D6D-6E77-4A34-8807-5724B2900FC4--