Sql error 42803 error aggregate functions are not allowed in where

I want to insert a tuple in my leggeapprovata table, but this table takes the attributes from 4 other tables. One of these 4 tables is the votazione table, from which it takes 6 attributes. Two do not give me errors, but for the others 4 attributes, it gives me the same error, ie that you can not use the aggregation function max in the where clause.

To avoid that the selection gives me more lines, since in the vote there are more tuples with different dates, I want the tuple with the most recent date (MAX (votazione.data)), but it gives me the error I was talking about before:

ERROR: the aggregation functions are not allowed in GROUP BY LINE 17: 
... ect votazione.favorevoli from votazione group by (MAX(vota ... ^
********** Error **********
ERROR: aggregate functions are not allowed in GROUP BY SQL state: 42803

I also tried with where, case when, having, but I still receive an error message.

Insert/Select statement

Following is the statement I am trying to execute.

INSERT INTO leggeapprovata (titolo, 
     relatore,
     numerolegislatura,
     testo,
     votisi,
     votino,
     astenuti,
     datapromulgazione,
     promulgatada,
     numlegge,
     dataapprovazionecamera,
     dataapprovazionesenato) 
  SELECT ddl.titolo, 
     ddl.relatore,
     legislatura.numero,
     ddl.testo,
     (select votazione.favorevoli from votazione group by (MAX(votazione.data)) AND tipoassemblea='senato'),
     (select votazione.contrari  from votazione group by (MAX(votazione.data)) AND tipoassemblea='senato'),
     (select votazione.astenuti  from votazione group by (MAX(votazione.data)) AND tipoassemblea='senato'),
     promulgazione.data,
     promulgazione.presidente,
     (select votazione.codice  from votazione  group by (MAX(votazione.data)) AND tipoassemblea='senato'),
     (select MAX(votazione.data) from votazione,ddl where tipoassemblea='camera' and votazione.ddl=ddl.titolo),
     (select MAX(votazione.data) from votazione,ddl where tipoassemblea='senato' and votazione.ddl=ddl.titolo)
   FROM ddl, legislatura, votazione, promulgazione
   WHERE ddl.promulgato='si' and ddl.titolo=promulgazione.ddl and (promulgazione.data between legislatura.datainizio and legislatura.datafine);

Table Definition

CREATE TABLE public.leggeapprovata
(
  titolo character varying(1000) NOT NULL,
  numerolegislatura integer NOT NULL,
  testo text,
  votisi integer,
  votino integer,
  astenuti integer,
  datapromulgazione date,
  promulgatada character varying(100),
  relatore character varying(100)[] NOT NULL,
  dataapprovazionecamera date,
  dataapprovazionesenato date,
  numlegge character(10),
  CONSTRAINT leggeapprovatapkey PRIMARY KEY (titolo, numerolegislatura, relatore),
  CONSTRAINT leggeapprovatanumerolegislaturafkey FOREIGN KEY (numerolegislatura)
      REFERENCES public.legislatura (numero) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT leggeapprovatapromulgatadafkey FOREIGN KEY (promulgatada)
      REFERENCES public.presidentedellarepubblica (presidente) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT leggeapprovatatestofkey FOREIGN KEY (testo, relatore)
      REFERENCES public.ddl (testo, relatore) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT leggeapprovatatitolofkey FOREIGN KEY (titolo, datapromulgazione)
      REFERENCES public.promulgazione (ddl, data) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT leggeapprovatavotisifkey FOREIGN KEY (votisi, votino, astenuti, numlegge)
      REFERENCES public.votazione (favorevoli, contrari, astenuti, codice) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT leggeapprovatatitolonumerolegislaturatestorelatorenukey UNIQUE (titolo, numerolegislatura, testo, relatore, numlegge)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.leggeapprovata
  OWNER TO postgres;
GRANT ALL ON TABLE public.leggeapprovata TO public;
GRANT ALL ON TABLE public.leggeapprovata TO postgres WITH GRANT OPTION;

Trigger Definitons

-- Trigger: inserimentoinleggeapprovata on public.leggeapprovata

-- DROP TRIGGER inserimentoinleggeapprovata ON public.leggeapprovata;

CREATE TRIGGER inserimentoinleggeapprovata
  BEFORE INSERT
  ON public.leggeapprovata
  FOR EACH ROW
  WHEN ((pg_trigger_depth() = 0))
  EXECUTE PROCEDURE public.inserimentoinleggeapprovata();
ALTER TABLE public.leggeapprovata DISABLE TRIGGER inserimentoinleggeapprovata;
COMMENT ON TRIGGER inserimentoinleggeapprovata ON public.leggeapprovata IS 'when (pg_trigger_depth()=)livello di annidamento corrente dei trigger PostgreSQL (0 se non viene chiamato, direttamente o indirettamente, da un trigger)';



CREATE TRIGGER inserimentoinleggeapprovata
  BEFORE INSERT
  ON public.leggeapprovata
  FOR EACH ROW
  WHEN ((pg_trigger_depth() = 0))
  EXECUTE PROCEDURE public.inserimentoinleggeapprovata();
COMMENT ON TRIGGER inserimentoinleggeapprovata ON public.leggeapprovata IS 'when (pg_trigger_depth()=)livello di annidamento corrente dei trigger PostgreSQL (0 se non viene chiamato, direttamente o indirettamente, da un trigger)';

Function Definitions

-- Function: public.inserimentoinleggeapprovata()

-- DROP FUNCTION public.inserimentoinleggeapprovata();

CREATE OR REPLACE FUNCTION public.inserimentoinleggeapprovata()
  RETURNS trigger AS
$BODY$
begin
if exists (select 1 from leggeapprovata
where leggeapprovata.titolo = NEW.titolo)
   THEN 
        RAISE EXCEPTION '% già ESISTENTE IN leggeapprovata', NEW.titolo;
   ELSE  RETURN new;
   end if;
end;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1000;
ALTER FUNCTION public.inserimentoinleggeapprovata()
  OWNER TO postgres;
GRANT EXECUTE ON FUNCTION public.inserimentoinleggeapprovata() TO postgres;
GRANT EXECUTE ON FUNCTION public.inserimentoinleggeapprovata() TO public;

I would appreciate any feedback regarding the error message.

Votazione Table Data

This is the first image of the votazione table:

This is the second image of the votazione table:

Pavel Stehule’s blog

Some notes about PostgreSQL

Monday, August 25, 2008

Using cursors for generating cross tables

I am working on procedure support in PostgreSQL more than one year. Missing procedures is one disadvantage of PostgreSQL. Because procedures are not called from SELECT statement, then it couldn’t respect some rules like functions. Procedures are mainly used for explicit transaction controlling and for generating mutable results (like dynamic record set or stacked record set). Dynamic record set is interesting feature when we nothing know about result set’s columns. It’s typical for cross tables. Because PostgreSQL doesn’t support procedures, we cannot return dynamic (mutable number of columns) queries directly, but we can return dynamic cursor. This method of generating cross tables is inspired by Roland Bauman’s http://rpbouman.blogspot.com blog.
Cross table is based on query’s pattern:

for data:
I write function, that generate necessary SELECT statement and open dynamic cursor.
Because cursors should be used only in transaction, I have to use explicit transaction:
There is result:
A alternative for this solution is using a tablefuc extension:

posted by Pavel Stěhule @ 2:54 AM 15 Comments

15 Comments:

At least on Firefox, the SQL text in the page is truncated. May it be possible to fix the CSS or to wrap the text so it is readable ?

Thank you for your valuable work on postgreSQL

At August 26, 2008 at 4:07 AM , Pavel Stěhule said.

I reformated article. Wrong style 🙁

At February 18, 2009 at 12:15 PM , Anonymous said.

Your article was very helpful to me, thanks a lot 🙂
w

At July 7, 2009 at 4:11 PM , Gustavo Straube said.

Very good article! It’s just what I’ve looking for.

I have one question: probably I’ll use this technique in an application I’m developing and want to know about the impact in terms of performance with a large ammount of data. Is it fine?

At July 7, 2009 at 10:38 PM , Pavel Stěhule said.

what I know, this method is the best on SQL level. But on very large tables this query should run long time. PostgreSQL missing OLAP or ROLAP support. Try it and you will see. This solution is very simple, but if it will be too slow you can use some general OLAP sw — Mondrian, Pentaho

At February 12, 2010 at 4:45 AM , Anonymous said.

How about implementing a new keyword in Postgres, like EXECUTE PROCEDURE ‘function_name()’ which returns «SETOF RECORD», and which is equivalent to SELECT * FROM ‘function_name()’, but doesn’t give you an error?

At February 12, 2010 at 4:58 AM , Pavel Stěhule said.

This is in my ToDo (when I meet some sponsor). It is standardized: CREATE PROCEDURE and CALL statement.

At July 19, 2010 at 6:29 PM , irman said.

Brilliant article. it is simpler to do crosstab ;;)

At June 11, 2012 at 9:23 PM , Prof. Adriano Aguzzi, MD PhD DVM h.c. FRCP FRCPath said.

I am a molecular biologist and have minimal Postgres knowledge, so my question is very naive. I pasted your code into pgAdmin sql window, and everything works fine, your function is being created. But when I execute your procedure, PG says «3 rows discarded» and shows nothing. I am sure that this is because of my ignorance — can you tell me how to do it right? My goal is to create simple dynamic pivot queries like with Access.

At June 11, 2012 at 11:33 PM , Pavel Stěhule said.

To Adriano: you can test it in console? What are you doing exactly? This is pgAdmin message or PostgreSQL message?

At June 18, 2012 at 10:07 PM , Prof. Adriano Aguzzi, MD PhD DVM h.c. FRCP FRCPath said.

Dear Pawel
thank you for your help. When I paste the function into pgAdmin without wrapping it into the transaction, it works fine. Within the transaction block, it rejects the output. I will try to read more about transactions in order to understand this behavior.
I have a big question though. Could you write a very short (3-4 lines) documentation of the parameters of your function? What is the admissible parameter syntax, etc? A million thanks in advance.

At June 18, 2012 at 10:23 PM , Prof. Adriano Aguzzi, MD PhD DVM h.c. FRCP FRCPath said.

well, I tried the following:

SELECT do_cross_cursor( ‘varid’,
‘FROM loggingdb_ips_boolean As log’,
‘to_char(ipstimestamp, »mon DD HH24h»)’,
‘FROM loggingdb_ips_boolean As log’,
‘COUNT(*)::integer’);
FETCH ALL FROM result;

But it aborts with the following message:
ERROR: aggregate function calls cannot be nested
SQL state: 42803
Context: PL/pgSQL function «do_cross_cursor» line 19 at OPEN

Does this mean that one cannot use aggregate functions within this cursor?

At June 20, 2012 at 1:32 AM , Pavel Stěhule said.

To Adriano: You can use aggregate function with cursors — but you cannot nested aggregates — if you can understand code — it is based on dynamic SQL — then some query is dynamically created — and it use SUM as example. When you use COUNT(*), then it creates query with ..SUM( .. COUNT(*)) that is not valid. If you can use COUNT, then you have to modify source code. /parameters — it is column name and source, column name and source.

At August 1, 2012 at 1:01 AM , Peter said.

Hi Pavel Stěhule
its a beautiful code to create cross relation pivot table. i need to change all diagonal values to ‘N/A’. i have been trying to make some changes but could not succeeded. can you plz tell me how can i do this.

At August 1, 2012 at 1:31 AM , Unknown said.

hi there . your blog has helped me alot thank you very much . now i need some altretaion in this besause my requirmnts are slightly different . my scenario is i have three columns say
A(text) B(text) Value(text)
AA1 AA1 0
AA2 AA1 100
AA3 AA1 100
AA1 AA2 100
AA2 AA2 0
AA3 AA2 100
AA1 AA3 100
AA2 AA3 100
AA3 AA3 0

now i want a cross table that looks like
AA1 AA2 AA3
AA1 0 100 100
AA2 100 0 100
AA3 100 100 0

and then it should replace o with not applicable (N/A) so i am looking a cross table like

AA1 AA2 AA3
AA1 N/A 100 100
AA2 100 N/A 100
AA3 100 100 N/A

i have achieved the table with zeros but i am unable to place N/A value where row and column name is same i,e fro AA1 to AA1 or AA2 to AA2 . thans i will wait for your reply

Subscribe to Post Comments [Atom]

Name: Pavel Stěhule Location: Prague, Česká republika, Czechia

Источник

I have the following data:

table: identifier

objectid    type    name            value
9345783451  GUID    msisdn          bc3b2286379da25fd6ef3bac120827589c783
9345783451  UMID    umid            f701a99c-96c9-4d4a-8508-932eeeaca77c
9345783451  UMDID   umdid           2840f267-4bdd-4af4-8b36-72badbce1e11
9345783451  UMDID   umdid           bbe467f5-0fc4-4624-8b01-dd656767d3b2
9345783451  GUID    encrypted       ZPmBF2Spq8DBX0wl
9345783451  UMDID   umdid           a4c6b3cb-938f-4ae5-8246-f2612ffd946b
9345783451  UMDID   umdid           2a8e5f98-5a74-431e-bbaf-7c75fdeb991a
9345783451  UMDID   umdid           269a39b9-1122-4d08-9ca4-36f6c8e00e8e
9345783451  COOKIE  amcv            39852788960115122553605179944081330813
9345783451  UMDID   umdid           7715969f-63ab-4540-952c-73beafb46024
9345783451  GROUPID ban-sha2        1d98f855e9a4fbeba1937f774b6dbab2ca194b5
9345783451  COOKIE  token           21agqB6x_H8.1575825731298.aXHr4GoVupopE
9345783451  GUID    acn             12433792
9345783451  UMDID   umdid           f2cf7402-21d2-44ea-b432-66e997cfebbf
9345783451  GUID    targetId        1255625699
9345783451  UMDID   umdid           b8d55cdd-4a95-4e07-8eeb-f281a0961a6a
9345783451  UMDID   umdid           af890608-b512-4a96-9274-f39f388ff442
9345783451  UMDID   umdid           0c55ecb1-e24e-419d-97a1-48f6eba45fe0

Then i do the following query:

select objectid,
           count(objectid) filter ( where type = 'GUID' )                                                                     as guid_count,
           count(objectid) filter ( where type = 'GUID' and (name in
                                                             ('acn', 'encrypted', 'kdid', 'msisdn', 'targetId')) ) as cuid_count,
           count(objectid) filter ( where name = 'umdid' )                                                                    as umdid_count
    from identifier
    where objectid = '9345783451'
    group by objectid;

The result is correct:

objectid    guid_count cuid_count umdid_count
9345783451  4          3          10

Then i add two column, name and value:

select objectid,
               count(objectid) filter ( where type = 'GUID' )                                                                     as guid_count,
               count(objectid) filter ( where type = 'GUID' and (name in
                                                                 ('acn', 'encrypted', 'kdid', 'msisdn', 'targetId')) ) as cuid_count,
               count(objectid) filter ( where name = 'umdid' )                                                                    as umdid_count,
               case
                   when identifier.name = 'acn' then identifier.value::text
                   else '' end                                                                                                                          as acn
        from identifier
        where objectid = '9345783451'
        group by objectid, name, value;

That return:

objectid   guid_count cuid_count umdid_count acn
9345783451  0          0         1           "" 
9345783451  1          0         0           "" 
9345783451  0          0         1           "" 
9345783451  0          0         0           "" 
9345783451  0          0         1           "" 
9345783451  1          1         0           12433792 
9345783451  0          0         1           "" 
9345783451  0          0         1           "" 
9345783451  1          1         0           "" 
9345783451  0          0         0           "" 
9345783451  0          0         1           "" 
9345783451  0          0         1           "" 
9345783451  0          0         0           "" 
9345783451  0          0         1           "" 
9345783451  0          0         1           "" 
9345783451  0          0         0           "" 
9345783451  1          1         0           "" 
9345783451  0          0         1           ""

"" empty string

What i expect is:

objectid    guid_count cuid_count umdid_count acn
9345783451  4          3          10          12433792

If i add group by on guid_count, cuid_count, and umdid_count:

[42803] ERROR: aggregate functions are not allowed in GROUP BY Position: 26

How to overcome this problem? Could you help point me where my mistake is?

Thank you.

Answer

You should use an aggregate for the added column, e.g.:

select objectid,
    count(objectid) filter ( where type = 'GUID' ) as guid_count,
    count(objectid) filter ( 
        where type = 'GUID' 
        and (name in ('acn', 'encrypted', 'kdid', 'msisdn', 'targetId')) 
        ) as cuid_count,
    count(objectid) filter ( where name = 'umdid' ) as umdid_count,
    max(case
        when identifier.name = 'acn' then identifier.value::text
        else '' end) as acn
from identifier
where objectid = '9345783451'
group by objectid;