Re: Aggregate SQL Help

"tacnaboyz" <fake.address_at_fake.fake> wrote in message news:<3F049.3311$2k.213455_at_newsread1.prod.itd.earthlink.net>...
> Thank you for your suggestion. However, I guess I could've made it more
> clear that the sample I gave was just a simplified example of what I'm
> actually trying to do. The biggest problem with the solution provided is
> that values are hard coded into the query whereas in reality I won't know
> the values. I was looking for more of a generic query that would give me
> max frequency of distinct values in a column.
>

Here, you may want to try this :

select state,sex from
(select state,sex,count(*) sex_cnt,
 max(count(*)) over (partition by state) max_cnt from t group by state,sex) where sex_cnt=max_cnt

Sex values aren't hard-coded here, it should always work if you have analytic functions on your Oracle server. The only problem is that if in some states you have the same max counts for several measures (sex in this case), they will all be reported.

HTH, Igor Izvekov.

> Your query might give me the ideas I need to accomplish this though. I'll
> try tomorrow when I get access to the server. In the meantime, if there are
> any other ideas, PLEASE let me know.
>
> Thanks
>
> > SELECT
> > MAX (
> > SELECT COUNT(*) FROM My_Table
> > WHERE Sex = 'M' AND State = Outer.State,
> > SELECT COUNT(*) FROM My_Table
> > WHERE Sex = 'F' AND State = Outer.State
> > )
> > FROM
> > My_Table Outer
> > GROUP BY
> > State;
> >
> > That is how to get the number. You can use DECODE() or CASE to
> > actually return M or F.
> >
> > Brian
> >
Received on Wed Aug 07 2002 - 16:30:22 CDT