Re: Use of HAVING (Select/Group By) on a output-name of an aggregate function causes SYNTAX ERROR

From: Magnus Hagander <magnus(at)hagander(dot)net>
To: "Carlos Sotto Maior (UOL)" <csotto(at)uol(dot)com(dot)br>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: Use of HAVING (Select/Group By) on a output-name of an aggregate function causes SYNTAX ERROR
Date: 2021-01-31 11:33:55
Message-ID: CABUevEz6rfHg2w1AWFM8HHK0zpuy_Q-BzudPRO8O=H2CL0k-Aw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

On Sun, Jan 31, 2021 at 1:01 AM Carlos Sotto Maior (UOL)
<csotto(at)uol(dot)com(dot)br> wrote:
>
> Thanks a lot Tom.
> I'll solve it with a subquery.

No need for a subquery. In your particular case, it should just work with:

SELECT count(*) as cnt, f1, f2, active
FROM public.z_having
GROUP BY f1, f2, active
HAVING active = True AND count(*) > 1
ORDER BY cnt DESC

It can get ugly if your aggregates are large and complex, but not for
a simple count(*)...

//Magnus

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2021-01-31 16:16:08 Re: BUG #16846: "retrieved too many tuples in a bounded sort"
Previous Message PG Bug reporting form 2021-01-31 09:09:49 BUG #16846: "retrieved too many tuples in a bounded sort"