how to do GROUP BY groupfunction

From: Markus Kramer <kramer_at_mpimg-berlin-dahlem.mpg.de>
Date: 1997/10/14
Message-ID: <34437001.41C6@mpimg-berlin-dahlem.mpg.de>#1/1

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