Re: Aggregate SQL Help

From: tacnaboyz <fake.address_at_fake.fake>
Date: Wed, 07 Aug 2002 03:40:47 GMT
Message-ID: <3F049.3311$2k.213455@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.

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 Tue Aug 06 2002 - 22:40:47 CDT