SELECT freq, COUNT(freq) FROM
( SELECT COUNT (character) freq FROM characters
GROUP BY character )
GROUP BY freq;
--
Kind reGards,
Gerard
(ghp_at_santens.be; ghp_at_skynet.be)
Markus Kramer <kramer_at_mpimg-berlin-dahlem.mpg.de> schreef in artikel
<34437001.41C6_at_mpimg-berlin-dahlem.mpg.de>...
> 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;
>
Received on Tue Oct 14 1997 - 00:00:00 CDT
