Re: Syntax error needs explanation

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: Rich Shepard <rshepard(at)appl-ecosys(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Syntax error needs explanation
Date: 2025-07-14 20:07:53
Message-ID: CAKFQuwZWnGB2weH87+z4zn8mTg83rso2Cy4P2=fqqcAM9p5iWg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Mon, Jul 14, 2025 at 12:59 PM Rich Shepard <rshepard(at)appl-ecosys(dot)com>
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 knowlege
> 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 = c.company_nbr
)
group by c.industry;

David J.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rich Shepard 2025-07-14 20:13:17 Re: Syntax error needs explanation [RESOLVED]
Previous Message David G. Johnston 2025-07-14 20:00:54 Re: Performance of JSON type in postgres