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