Received: from hub.org.org (localhost [127.0.0.1]) by hub.org (8.10.1/8.11.0) with SMTP id e9QKlqU18012 for ; Thu, 26 Oct 2000 16:47:52 -0400 (EDT) (envelope-from pgsql-sql-owner@hub.org) Received: from berlin.cms.imrglobal.com (access-12.80.rev.fr.colt.net [213.41.80.12] (may be forged)) by hub.org (8.10.1/8.11.0) with ESMTP id e9QErxU77688 for ; Thu, 26 Oct 2000 10:53:59 -0400 (EDT) (envelope-from mpitot@CMS.IMRGlobal.com) Received: from cms29 (cms29.atechsys [10.1.1.29]) by berlin.cms.imrglobal.com (8.9.3/8.9.3) with SMTP id QAA26888 for ; Thu, 26 Oct 2000 16:19:48 +0200 Reply-To: From: "Pitot" To: Subject: Nested Aggregate fonction & gouping attributes Date: Thu, 26 Oct 2000 16:53:15 +0200 Message-ID: <000f01c03f5c$7905d3a0$1d01010a@cms29.atechsys> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3 X-Archive-Number: 200010/252 X-Sequence-Number: 1919 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;