Re: how to do GROUP BY groupfunction

From: Chrysalis <cellis_at_iol.ie>
Date: 1997/10/14
Message-ID: <3443FCD2.5FA6@iol.ie>#1/1

Markus Kramer wrote:
>
> Hi,
>
> The code at the end of this posting returns
> FREQ COUNT(FREQ)
> ---------- -----------
> 1 2
> 2 2
> 4 1
>
> meaning that there are 2 chars occuring once, another 2 occuring twice
> and a single character occuring four times. (which is correct)
>
> My question is: do I really have to use a temporary table?
>
> The best-of-all-world solution (I can think of) results in:
>
> GROUP BY character, COUNT (character)
> *
> ERROR at line 3:
> ORA-00934: group function is not allowed here
>
>
> greetings.
>
> -------------feed me into sqlplus-------------------
> CREATE TABLE characters (character CHAR);
> CREATE TABLE freqtable (freq NUMBER);
>
> INSERT INTO characters VALUES ('a');
> INSERT INTO characters VALUES ('a');
> INSERT INTO characters VALUES ('b');
> INSERT INTO characters VALUES ('b');
> INSERT INTO characters VALUES ('c');
> INSERT INTO characters VALUES ('d');
> INSERT INTO characters VALUES ('e');
> INSERT INTO characters VALUES ('e');
> INSERT INTO characters VALUES ('e');
> INSERT INTO characters VALUES ('e');
>
> INSERT INTO freqtable
> SELECT COUNT (character) FROM characters
> GROUP BY character
> ;
>
> SELECT freq, COUNT(freq) FROM freqtable
> GROUP BY freq
> ORDER BY freq;
>
> DROP TABLE freqtable;
> DROP TABLE characters;
> EXIT;
Two ways:

I.
select freq,count(*)
from (select count(*) freq

      from   characters
      group by character)

group by freq;

II.
Define a VIEW with the select statement in the above subquery and then execute the outer query as if the view were a table.

HTH

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"

Received on Tue Oct 14 1997 - 00:00:00 CDT