pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedError message: standard SQL or PostgreQSL?
3+ messages / 3 participants
[nested] [flat]
* Error message: standard SQL or PostgreQSL?
@ 2002-11-15 06:31 Huub <v.niekerk@freeler.nl>
2002-11-15 15:20 ` Re: Error message: standard SQL or PostgreQSL? Tom Lane <tgl@sss.pgh.pa.us>
2002-11-15 15:42 ` Re: Error message: standard SQL or PostgreQSL? Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 2 replies; 3+ messages in thread
From: Huub @ 2002-11-15 06:31 UTC (permalink / raw)
To: pgsql-sql
Hi,
When I copied a function call into an SQL-statement and executed it, I
got the message 'Aggregate function calls may not be nested'. Does that
mean this is the case in standard SQL or is this specifically PostgreSQL?
Thanks
Huub
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Error message: standard SQL or PostgreQSL?
2002-11-15 06:31 Error message: standard SQL or PostgreQSL? Huub <v.niekerk@freeler.nl>
@ 2002-11-15 15:20 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2002-11-15 15:20 UTC (permalink / raw)
To: Huub <v.niekerk@freeler.nl>; +Cc: pgsql-sql
Huub <v.niekerk@freeler.nl> writes:
> When I copied a function call into an SQL-statement and executed it, I
> got the message 'Aggregate function calls may not be nested'. Does that
> mean this is the case in standard SQL or is this specifically PostgreSQL?
It's in the standard: SQL92 (which calls aggregates "set functions")
says
4) The <value expression> simply contained in <set function spec-
ification> shall not contain a <set function specification> or
a <subquery>.
I don't see a reason for the <subquery> prohibition (and AFAIK that case
will work in Postgres), but the rule against nested aggregate calls is
simply common sense when you think about it. What would it mean to nest
two aggregate functions? For example, in
SELECT COUNT(a) + 1, SUM(b + c) FROM ...
for each row produced by the FROM-clause, we check the null-ness of "a"
(and increment the count or not), and we form b+c and add it to the
running sum. At the end of the input we output the result row. Notice
that "b+c" is evaluated at each input row but the +1 is only done once,
because one is inside and the other is outside the aggregate calls.
Now, what would
SELECT SUM(a + SUM(b + c)) FROM ...
actually mean? If the inner SUM() ranges over all the rows, what value
of "a" is supposed to be added to it? If it doesn't range over all the
rows, then what's it doing? How would you control what set of rows each
SUM() is ranging over?
So in general it makes no sense to nest aggregates in a single SELECT
list. There are cases where you want to do something that amounts to
nesting aggregates, but you always have to write multiple levels of
SELECT in order to express the behavior you want. For example, suppose
I want to know the average total payroll of all company departments:
SELECT AVG(deptpay) FROM
(SELECT SUM(pay) AS deptpay FROM employees GROUP BY dept) ss;
Here, the inner SELECT produces a row for each department, with the
SUM() ranging over all rows having that department ID; then the outer
SELECT's AVG() ranges over the per-department rows.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Error message: standard SQL or PostgreQSL?
2002-11-15 06:31 Error message: standard SQL or PostgreQSL? Huub <v.niekerk@freeler.nl>
@ 2002-11-15 15:42 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2002-11-15 15:42 UTC (permalink / raw)
To: Huub <v.niekerk@freeler.nl>; +Cc: pgsql-sql
On Fri, 15 Nov 2002, Huub wrote:
> Hi,
>
> When I copied a function call into an SQL-statement and executed it, I
> got the message 'Aggregate function calls may not be nested'. Does that
> mean this is the case in standard SQL or is this specifically PostgreSQL?
Umm, both sort of. It's an entry level SQL92 restriction IIRC, so
both allowing it and not allowing it are standard SQL to some degree.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-11-15 15:42 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-15 06:31 Error message: standard SQL or PostgreQSL? Huub <v.niekerk@freeler.nl>
2002-11-15 15:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-11-15 15:42 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
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