From: Pitot <mpitot@CMS.IMRGlobal.com>
To: pgsql-sql@postgresql.org
Subject: Nested Aggregate fonction & gouping attributes
Date: Thu, 26 Oct 2000 16:53:15 +0200
Message-ID: <000f01c03f5c$7905d3a0$1d01010a@cms29.atechsys> (raw)
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: mpitot@CMS.IMRGlobal.com
Subject: Re: Nested Aggregate fonction & gouping attributes
In-Reply-To: <000f01c03f5c$7905d3a0$1d01010a@cms29.atechsys>
References: <000f01c03f5c$7905d3a0$1d01010a@cms29.atechsys>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox