Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ubPTZ-000Sdi-QF for pgsql-general@arkaria.postgresql.org; Mon, 14 Jul 2025 20:08:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1ubPTW-00DDH5-LF for pgsql-general@arkaria.postgresql.org; Mon, 14 Jul 2025 20:08:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ubPTW-00DDGu-A4 for pgsql-general@lists.postgresql.org; Mon, 14 Jul 2025 20:08:34 +0000 Received: from mail-oi1-x235.google.com ([2607:f8b0:4864:20::235]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1ubPTU-007iaX-2Q for pgsql-general@postgresql.org; Mon, 14 Jul 2025 20:08:34 +0000 Received: by mail-oi1-x235.google.com with SMTP id 5614622812f47-41b4ebb157eso278920b6e.3 for ; Mon, 14 Jul 2025 13:08:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1752523710; x=1753128510; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=/ZzMDEeieMSK5Sm0ZissszE6o8AN2DXdlROPPTO1Is0=; b=Idu0dYQt5fsDv8azIjtEyMB3bdwHEoJSeiOzo+a/xN4uRn2Et089ahLsJkBvbxt96w mLZNX6yhl/+2PsxSSeJj6nmkl1MQerg5KsRrNfhZvfug77tYlCWUjY0gZpSLXJXPH84H B4sy8MwGADZNEgDZMjRnG6tSPVjM+t/2f2ZWq7qgJOspVAtFWn2mHnCU0x3y7sUGDqPZ o2yttVL0Ws+W5uCRxuU4HLHY4PugEGzeOg//yHAdSkNKyTZm3pWbYxmr1BJRr08HJelM tvRv+/WnGI9r0sl3Wu8N0dTl2xEfrf+nd2C1qn4CWcQOl1+Tzf3ZGdcrwAmmFbX8H/Oz Xt7w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1752523710; x=1753128510; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=/ZzMDEeieMSK5Sm0ZissszE6o8AN2DXdlROPPTO1Is0=; b=MawRyqlCS30kpxdTo+lPEP8Nr8xR/BmqEaLB6DrYB+sH8O2utHBBhX59h2Ioi0SQER FalCo5wzdDcVV7dxtpFu3gaKeTrnKpOzsaaodYnd4qHzQZbOBJjtzPqj/LXBJP0bTN4G XLRyUwfPk80qf1/5AdL5CzfmmwHjkaInRxonm1mggyqGpSXDJDJjNbyuAwmngkUXJKHV sfIzkV1ZPWRlUth9QtELaXXDYNBwUERWSZXFAIRZzQjp2EnUQ8TXMs5ByIjCip463XK1 DLzQjM7LT9BRV79Em9F0ETvMILw5AF9z39jku4YcLjoAnDnmuSIhCUbJsxxY7RfeSAen /KGQ== X-Gm-Message-State: AOJu0YyzQ/jlzEeXZYK5ymdFwRncHYLQ0gQ9es6LVI7CTTGlsDyb3fCx BP23Iw5dVCQI54oVTsbo8cdHmlSBKwUHYo+1mpGKVqxSWpi6uJGIqiEQMq0hXl9Vm3DCyOmzNfP 4via2tcqSLoVHx4MUJpF+zaqrffZLERGfsNjz X-Gm-Gg: ASbGncuSXrhZz6V6EqjZZRGyg4E0n+PcNzI0kggX57ncqkjjdaz+Jhro4TU5GZsX0d9 h1zTHISK1kdIFExcyUYyi3+paPg38SdF7mWvpENuxq8AErNO7MEdWEc+H/mnp8EKA6SjvFtp271 KgulqWvKy4JnsaIJdyNRGYC3fPFrtOcBIr53e65RTjVZdVV9ma9Z+x1dq1cFDIKdvKcPGvxZoOM Hn8NMG/0pamB2T4GVfFHl1WgCVnHIYGQSQDVko= X-Google-Smtp-Source: AGHT+IFk6NnnE7MAh9LXSWeu9ygYS1YdesIMt4RO89hQuMYYIpelXXoHiePWYXwdNbedBhOhqB4cK4YEKbCm+K50TbM= X-Received: by 2002:a05:6808:16aa:b0:40b:1222:8fd8 with SMTP id 5614622812f47-41539f925c2mr10529607b6e.35.1752523709750; Mon, 14 Jul 2025 13:08:29 -0700 (PDT) MIME-Version: 1.0 References: <9c84776d-d938-9fa4-ab1-6ee960ce6e7d@appl-ecosys.com> <7ec33517-6dc1-4e1d-b741-0c26eba7cc79@aklaver.com> <6684eaa7-e6e2-a45b-cff9-44597ca3c8ad@appl-ecosys.com> In-Reply-To: <6684eaa7-e6e2-a45b-cff9-44597ca3c8ad@appl-ecosys.com> From: "David G. Johnston" Date: Mon, 14 Jul 2025 13:07:53 -0700 X-Gm-Features: Ac12FXxjzf0ntkL2ERnWuCjXuCqDwJDG3TxUBWp3rFATrgkrVzXffCbW911mwUQ Message-ID: Subject: Re: Syntax error needs explanation To: Rich Shepard Cc: pgsql-general Content-Type: multipart/alternative; boundary="0000000000002e26b30639e939bc" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002e26b30639e939bc Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Jul 14, 2025 at 12:59=E2=80=AFPM Rich Shepard wrote: > > The current version of the script: > > select c.company_nbr, c.company_name, c.industry > from companies as c > where exists ( > select e.company_nbr > from enforcement as e > ) > group by c.industry > order by c.industry; > > And psql tells me that c.company_nbr must be in the group by clause. > However, when I do that the output is a list of company numbers and names > in > each industry. > > My web searches on using the exists operator haven't provided the knowleg= e > for me to use it properly. > > Yeah, you need both to read up on aggregate queries and correlated subqueries which is typically how one makes uses of exists (it's called a semi-join in this formulation) Not tested, but: select c.industry, count(*) from companies as c where exists ( select from enforcement as e where e.company_nbr =3D c.company_nbr ) group by c.industry; David J. --0000000000002e26b30639e939bc Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Jul 14, 2025 at 12:59=E2=80=AFPM Rich Shepard <= rshepard@appl-ecosys.com>= ; wrote:

The current version of the script:

select c.company_nbr, c.company_name, c.industry
from companies as c
where exists (
=C2=A0 =C2=A0select e.company_nbr
=C2=A0 =C2=A0from enforcement as e
=C2=A0 =C2=A0)
group by c.industry
order by c.industry;

And psql tells me that c.company_nbr must be in the group by clause.
However, when I do that the output is a list of company numbers and names i= n
each industry.

My web searches on using the exists operator haven't provided the knowl= ege
for me to use it properly.


Yeah, you = need both to read up on aggregate queries and correlated subqueries which i= s typically how one makes uses of exists (it's called a semi-join in th= is formulation)
Not tested, but:

select c.in= dustry, count(*)
from companies as c
where exists (
select from enforcement as e
where e.company_nbr =3D c.company_nbr=
)
group by c.industry;

David J.
=


--0000000000002e26b30639e939bc--