Subject: Re: Syntax error needs explanation

From: "David G. Johnston" <[email-masked]>
Date: Mon, 14 Jul 2025 12:14:47 -0700

On Mon, Jul 14, 2025 at 12:12 PM Rich Shepard <[email-masked]>
wrote:

> I have the following script:
>
> select c.company_nbr, c.company_name, i.industry,
> from companies as c, industry as i, enforcement as e
> where exists (
>    select c.company_nbr, count(e.action_date), sum(e.penalty_amt)
>    from e.enforcement
>    where c.company_nbr = e.company_nbr
>    )
> group by industry
> order by industry;
>
> When I run it psql reports an error:
> psql:companies-with-enforcement-actions.txt:127: ERROR:  syntax error at
> or near "company_nbr"
> LINE 1: company_nbr |               company_name
>          ^
> and I'm not seeing the error. What am I missing?
>

The error indicates your script file is at least 127 lines long and you are
showing like 9...also do you usually name your script files with a .txt
extension?

David J.

---

From: Rich Shepard <[email-masked]>
Date: Mon, 14 Jul 2025 12:19:44 -0700 (PDT)

On Mon, 14 Jul 2025, David G. Johnston wrote:

> The error indicates your script file is at least 127 lines long and you
> are showing like 9...also do you usually name your script files with a
> .txt extension?

David J.,

Agh! No the filename extension is .sql. But I was using the \o psql option
to write script output to files and mistyped the script name.

Mea culpa!

Many thanks,

Rich

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 15 Jul 2025 07:46:43 +0200

> to write script output to files and mistyped the script name.

Apart from that, the subquery seems to be missing a GROUP BY clause.

Yours,
Laurenz Albe

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 14 Jul 2025 22:55:33 -0700

> Apart from that, the subquery seems to be missing a GROUP BY clause.

Well, it’s more that an exists subquery with an aggregate generally doesn’t
make sense (it would need to include a having clause at minimum)…it’s not
missing a group by clause, it has aggregates it doesn’t need (they belong
in they belong in the main query where the group clause exists without
aggregates to justify its existence.

David J.

---

From: Rich Shepard <[email-masked]>
Date: Tue, 15 Jul 2025 05:40:26 -0700 (PDT)

> Apart from that, the subquery seems to be missing a GROUP BY clause.

Laurenz,

That was added.

Thanks,

Rich