Re: Help with aggregate SQL

"tacnaboyz" <fake.address_at_fake.fake> wrote :

> 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

Interesting.

I would try changing the GENDER column (not SEX - that is always value "Y" ;-) to a numeric value and then summing it.

Something like:
SELECT
  state,
  SUM( DECODE(gender, 'M', 1, -1) ) "GENDER_INDIC" FROM foobar
GROUP BY state

A negative sum indicates more females. A positive sum indicates more males. A zero indicates that both groups are equal in numbers. An interesting side effect of this is that the abs of the sum indicates the actual number of males/females more.

You can also decode the GENDER_INDIC sum - a bit of a hack, but by doing a TO_CHAR() you will get a space or minus sign as the first char. You can SUBSTR() that 1st char and DECODE() it. If it is a space, then make the result "M", else it will be a minus and thus "F".

You can also use more measures to count the actual total of each gender e.g. SELECT
  state,
  SUM( DECODE(gender, 'M', 1, 0) ) "MALES",   SUM( DECODE(gender, 'F', 1, 0) ) "FEMALES" FROM foobar
GROUP BY state

The only problem with this approach is that the DECODE is Oracle specific and will not work on other SQL-based databases. Even so - other SQL implementations have a similar proprietary/native IF-THEN_ELSE encoding/decoding functions.

--
Billy