Re: Help with aggregate SQL

Billy's solution of DECODE is the best for a two way solution - very neat. You mention that you want an even more generic approach for columns with more diverse values (3 or more) using one SQL statement so you might want to try the following: -

select x.state state, x.flag flag, x.cnt xcnt from  (
 select state, flag, count(*) cnt
 from test
 group by state, flag
 ) x
where not exists (
 select flag, count(*) ycnt
 from test y
 where y.state = x.state
 group by flag
 having count(*) > x.cnt
 )

This assumes that flag is the column you're interested in much like Gender in your example. If you've not got an index on state and flag then performance won't be ideal in large tables due to two full table scans.

Andy

"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!!!
>
>
>
Received on Wed Aug 07 2002 - 03:57:25 CDT