Re: Help with aggregate SQL

From: Nigel Puntridge <nigel_puntridge_at_hotmail.com>
Date: Wed, 7 Aug 2002 09:10:15 -0400
Message-ID: <3d512b89$1_4@news.teranews.com>

SQL> CREATE TABLE SEX_STATE
  2 (
  3 FNAME VARCHAR2(30),
  4 LNAME VARCHAR2(30),
  5 SEX VARCHAR2(30),
  6 STATE VARCHAR2(2)
  7 );

Table created.

SQL>
SQL> INSERT INTO SEX_STATE VALUES('Beam','Jim','F','AK');

1 row created.

SQL>
SQL> INSERT INTO SEX_STATE VALUES('Hackett','Buddy','F','AK');

1 row created.

SQL>
SQL> INSERT INTO SEX_STATE VALUES('Reno','Janet','M','AK');

1 row created.

SQL>
SQL> INSERT INTO SEX_STATE VALUES('Sexy','Sally','M','NY');

1 row created.

SQL>
SQL> COMMIT; Commit complete.

SQL>
SQL> SELECT * FROM SEX_STATE;

FNAME                          LNAME                          SEX

ST

------------------------------ ------------------------------ --------------
---------------- --
Beam                           Jim                            F
AK
Hackett                        Buddy                          F
AK
Reno                           Janet                          M
AK
Sexy                           Sally                          M

NY

SQL>
SQL> SELECT STATE,SEX,MAX(SEXCOUNT)
  2 FROM
  3 (
  4 SELECT
  5 STATE
  6 ,SEX
  7 ,COUNT(*) AS SEXCOUNT
  8 FROM SEX_STATE I
  9 GROUP BY STATE,SEX
 10 HAVING COUNT(*) = (SELECT

 11                           MAX(COUNT(*))
 12                           FROM SEX_STATE AA
 13                           WHERE AA.STATE = I.STATE
 14                           GROUP BY AA.STATE,SEX)

 15 ) A
 16 GROUP BY STATE,SEX;

ST SEX                            MAX(SEXCOUNT)
-- ------------------------------ -------------
AK F                                          2
NY M                                          1

Received on Wed Aug 07 2002 - 08:10:15 CDT