Re: Aggregate SQL Help

On 6 Aug 2002 12:22:11 -0700, tacnaboyz_at_yahoo.com (tacnaboyz) wrote:

>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 | M | AK
>Buddy | Hackett | M | AK
>Janet | Reno | F | AK
>Sexy | Sally | F | 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 | M
>NY | F
>
>I'm thinking I can somehow do this with some of Oracle's aggregate
>functions, but I'm not seeing it.
>
>Any ideas?
>
>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 - 14:40:17 CDT