| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Complex decode help needed
A copy of this was sent to c699484_at_showme.missouri.edu
(if that email address didn't require changing)
On Wed, 24 Jun 1998 21:12:04 GMT, you wrote:
>I'm trying to put together a report that is basically a table summarazing the
>ethnicity of some members of a database. Let's say I have a table like this:
>
> ID ETHNICITY AGE SEX
> == ========= === ===
> 1 0 30 M
> 2 3 22 F
> 3 3 24 M
> 4 1 41 M
> 5 2 33 F
>
>Where ethnicity is a code single character code from a lookup table.
>
>I need to generate a report that looks something like this
>
> 25 & Under 26 thru 30 31 thru 40 41 & over
> Ethnicity Male | Fem. Male | Fem. Male | Fem. Male | Fem
> ========= =========== =========== =========== ===========
> 0 1
> 1 1
> 2 1
> 3 1 1
>
>
>I should note that this is simplified, I have 7 ethnicity codes and the age
>is actually just a birthdate stored as SYSDATE. NULL's are concievable, so I
>need to be sure that the statement doesn't return a value for a null field.
>Before discovering the power of decode, I created 70 some select count
>statements that correctly got each count..But that's WAY too many queries for
>our server, not to mention the time it'll take to generate that report.
>
>Which finally brings me to my question; how can decode help me here? I'd
>have to nest a number of decode statements...Most of the examples I've seen
>are used to return a numeric code for each text string...I want to get a
>count for each column in that table.
>
decodes can help you classify the data into columns you can group on and then it can help you pivot the data. I created a table just like yours (kept age as a number, thats up to you to change age in the query to something like "trunc( months_between( sysdate, birth_date) / 12 )" to get the age or however you compute it)...
so, the query is:
SQL> column M1 format 999999 heading "Male|25+under" SQL> column F1 format 999999 heading "Female|25+under" SQL> column M2 format 999999 heading "Male|26-30" SQL> column F2 format 999999 heading "Female|26-30" SQL> column M3 format 999999 heading "Male|31-40" SQL> column F3 format 999999 heading "Female|31-40" SQL> column M4 format 999999 heading "Male|41+over" SQL> column F4 format 999999 heading "Female|41+over" SQL> SQL> SQL> select ethnicity, 2 sum(decode( grouping, 'M1', 1, 0 )) M1, 3 sum(decode( grouping, 'F1', 1, 0 )) F1, 4 sum(decode( grouping, 'M2', 1, 0 )) M2, 5 sum(decode( grouping, 'F2', 1, 0 )) F2, 6 sum(decode( grouping, 'M3', 1, 0 )) M3, 7 sum(decode( grouping, 'F3', 1, 0 )) F3, 8 sum(decode( grouping, 'M4', 1, 0 )) M4, 9 sum(decode( grouping, 'F4', 1, 0 )) F410 from
13 decode( sign(age-26), -1, '1', 14 decode( sign(age-31), -1, '2', 15 decode( sign(age-41), -1, '3', '4' ) ) ) grouping16 from data
Male Female Male Female Male Female Male Female ETHNICITY 25+under 25+under 26-30 26-30 31-40 31-40 41+over 41+over ---------- -------- -------- ------- ------- ------- ------- ------- -------
0 0 0 1 0 0 0 0 0
1 0 0 0 0 0 0 1 0
2 0 0 0 0 0 1 0 0
3 1 1 0 0 0 0 0 0
It uses an inline view to classify the data. We create values M1, F1 .. M4, F4 representing the 8 groups you want to report on. We then use a trick with decode and sum to transpose rows into columns and group by ethnicity.
This works with nulls and as many ethnicity codes as you have...
>Can someone get me started? I really appreciate it, and I know it's
>inconsiderate to ask this kind of question without posting code I've tried,
>but trust me, it wasn't even close to correct (:
>
> -Scott McCool
>
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jun 24 1998 - 20:50:04 CDT
 |
 |