Re: Aggregate SQL Help

Brian Tkatch <brian.tkatch_at_shopsforme.com> wrote in message news:<t590lusicpgllig07sl2e5njjqi2e7ndkv_at_4ax.com>...
> 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

Had no luck with this query, probably needs some tuning or I missed something. Here's what I'd do. Comments are welcome.

select a.state,nvl(b.f_cnt,0),nvl(c.m_cnt,0) from

(select distinct state from t) a,
(select state,count(*) f_cnt from t where sex='F' group by state) b,
(select state,count(*) m_cnt from t where sex='M' group by state) c
where b.state(+)=a.state and
      c.state(+)=a.state;

You probably don't need table "a" and nvl functions if all states have both males and females. Again, you would need decode or case to report major sex.

HTH,
Igor Izvekov. Received on Wed Aug 07 2002 - 09:50:24 CDT