Nested Aggregate fonction & gouping attributes - Mailing list pgsql-sql

From Pitot
Subject Nested Aggregate fonction & gouping attributes
Date
Msg-id 000f01c03f5c$7905d3a0$1d01010a@cms29.atechsys
Whole thread Raw
List pgsql-sql
In my SELECT statement I fetch data from a view, and I have the 2 following
errors:
1.When I use Aggregate function AVG, it returns "Aggregate function calls
may not be nested"
2. When I use my PL/PGSQL function "HO()" it returns "Attribute
req_preminter.datedemande must be GROUPed or used in an aggregate function"

It must be a commun mistake I'm sure,but I'm not very good at big queries (I
thought I was once for all succesfull by creating views...) ; I've read that
subselect fct shoud be used instead, but how, and what is a grouped
attribute ?
Can somebody help me ?

----------------------------------------------
Here is my queries, if interested
----------------------------------------------
CREATE VIEW Req_PremInter AS SELECT Demande.demandeID, to_char(datedemande,
'MMYYYY') AS Mois, Demande.Datedemande AS Datedemande,
Min(Intervention.DateInter) AS DateInter FROM Demande INNER JOIN
Intervention ON Demande.demandeID = Intervention.Demande GROUP BY
Demande.demandeID, to_char(datedemande, 'MMYYYY'), Demande.Datedemande;

QUERY with AVG:
--------------------------
SELECT Req_PremInter.Mois AS Mois, Avg(to_number(dateinter,99)) AS Delai
FROM Req_PremInter GROUP BY Req_PremInter.Mois ORDER BY Req_PremInter.Mois;

QUERY with HO (PL/PGSQL function)
-------------------------
SELECT Req_PremInter.Mois AS Mois, HO(datedemande, dateinter, '12/12/2000',
'12/12/2000')/24 AS Delai FROM Req_PremInter GROUP BY Req_PremInter.Mois
ORDER BY Req_PremInter.Mois;

Final QUERY (what I really what to run)
--------------------------------------------------------
SELECT Req_PremInter.Mois AS Mois,
Avg(HO(datedemande,'12/12/2000',dateinter,datedemande)/24) AS Delai FROM
Req_PremInter GROUP BY Req_PremInter.Mois ORDER BY Req_PremInter.Mois;



pgsql-sql by date:

Previous
From: Ulf Mehlig
Date:
Subject: "average" time
Next
From: Michael Teter
Date:
Subject: renaming columns... danger?