Re: Help with aggregate SQL

"tacnaboyz" <fake.address_at_fake.fake> wrote in message news:Sl149.3421$2k.221646_at_newsread1.prod.itd.earthlink.net...
> Hello everyone,
> I'm thinking this must be relatively simple, but my brain is not
> cooperating. Let's say I had a table like this...
>
> FirstName | LastName | Sex | State
> ----------|----------|-----|------
> Jim | Beam | F | AK
> Buddy | Hackett | F | AK
> Janet | Reno | M | AK
> Sexy | Sally | M | NY
>
> For each state, I want to know if there are more males or more
> females.... and for my query, the only column I can have in my "GROUP
> BY" clause is "STATE". So I need something along these lines (though
> obviously incorrect)....
>
> SELECT STATE, MAX(SEX) AS MAJORITY_SEX FROM MY_TABLE GROUP BY STATE
>
> I would like to see a query result of...
>
> STATE | MAJORITY_SEX
> ------|-------------
> AK | F
> NY | M
>
> I'm thinking I can somehow do this with some of Oracle's aggregate
> functions, but I'm not seeing it. Also, please keep in mind, that I'm not
> looking for a solution for this particular example (I just made it up for
> illustration)... I'm looking for how to make queries that figure out the
> maximum frequency of distinct values in a column, grouped by values of
> another column.
>
> Any ideas?
>
> Thanks!!!

One solution is to use the RANK function, but with the data example you describe it will involve a nested query e.g.

SELECT b.state, b.sex AS majority_sex
FROM(
SELECT a.state, a.sex, a.population, rank() over (PARTITION BY a.state ORDER BY a.population DESC) AS rk FROM(
SELECT o.state, o.sex, COUNT(*) AS population FROM my_table o
GROUP BY o.state, o.sex)a)b
WHERE b.rk = 1

Paul Dixon Received on Wed Aug 07 2002 - 04:49:25 CDT