Use of an aggregate function on SELECT/GROUP By is a common need.
Also is common to use a friendly output-name like “cnt” for count(*).
But trying to filter results with HAVING <condition> will give SYNTAX error if the output-name is used as argument.
Comment: Although column <cnt> does not exists in table it is present in the Group By result that will be filtered by HAVING clause.
In other words: Postgresql is matching <HAVING> arguments to columns at table level instead of matching to a group by result level.
Thanks for the attention.
Carlos Sotto Maior
--SELECT VERSION() ==> PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
-- Table: public.z_having
-- DROP TABLE public.z_having;
CREATE TABLE public.z_having
(
id integer NOT NULL,
f1 integer,
f2 integer,
payload character varying COLLATE pg_catalog."default",
active boolean,
CONSTRAINT pk_z_having_id PRIMARY KEY (id)
)
TABLESPACE pg_default;
ALTER TABLE public.z_having OWNER to postgres;
COMMENT ON TABLE public.z_having
IS 'Use of Group By with count agregate with output-name (AS clause) is OK\nBut If HAVING clause is used to filter on output-name (cnt) you get a syntax error';
INSERT INTO public.z_having(id, f1, f2, payload, active) VALUES (1, 10, 10, 'aaaaa', false);
INSERT INTO public.z_having(id, f1, f2, payload, active) VALUES (2, 10, 20, 'aaaaa', true);
INSERT INTO public.z_having(id, f1, f2, payload, active) VALUES (3, 10, 30, 'aaaaa', false);
INSERT INTO public.z_having(id, f1, f2, payload, active) VALUES (4, 10, 10, 'aaaaa', true);
INSERT INTO public.z_having(id, f1, f2, payload, active) VALUES (5, 10, 20, 'aaaaa', true);
INSERT INTO public.z_having(id, f1, f2, payload, active) VALUES (6, 10, 30, 'aaaaa', true);
-- GROUP BY WORKS OK --
SELECT count(*) as cnt, f1, f2, active
FROM public.z_having
GROUP BY f1, f2, active
ORDER BY cnt DESC
-- Adding HAVING clause on output-name for the count() aggregate function results in SYNTAXerror
SELECT count(*) as cnt, f1, f2, active
FROM public.z_having
GROUP BY f1, f2, active
HAVING active = True AND cnt > 1
ORDER BY cnt DESC
--ERROR MESSAGE *******
--ERROR: column "cnt" does not exist
--LINE 5: HAVING active = True AND cnt > 1
^
--SQL state: 42703
--Character: 135