Re: sql syntax - use rollup ?

From: Phil Haigh <phil.haigh_at_fwpharma.com>
Date: Tue, 29 May 2001 09:31:37 +0100
Message-ID: <L7JQ6.12785$8Z5.846199@monolith.news.easynet.net>

>but can I do it with some variation of rollup?
>
>select fk_column, avg(count(1)) as anaverage
>from sometable group by rollup (fk_column);
>
>does not work..
>

To use GROUP BY, you must include all non-group columns in the group by clause. Your GROUP BY specifies a function that is not in the select statement, which is why it is not legal. Also your use of ROLLUP is incorrect - this query option can only in the GROUP BY clause as far as I'm aware. Check the Oracle SQL Reference - Chapter 4 - for examples on how to use ROLLUP (under the grouping explanation).

Phil Haigh
Oracle Certified Professional - Developer 6i Received on Tue May 29 2001 - 03:31:37 CDT

Re: sql syntax - use rollup ? - avg of count(1)