Re: More complex decode help needed

>Subject: More complex decode help needed
>From: ddreams_at_my-dejanews.com
>Date: 6/29/98 08:06 GMT
>Message-id: <6n8s3m$tns$1_at_nnrp1.dejanews.com>
>
>
>
>Hi .... I'm working with Oracle Reports do develop an ethnicity report. The
>relevent parts of the table look like this:
>
>SEX | ETHNIC_ORIGIN | AGE | STUDY_TIME
>==============================================
>M 1 22 F
>F 1 24 F
>M 3 21 F
>M 4 30 P
>F 4 38 P
>F 23 F
>
>Sex is either 'M' or 'F', ethnic origin is a numeric code which also has a
>lookup table, age is sysdate, but converted with a trunc(), study time is
>either F (full time) or P (part time).
>
>I am currently using a decode statement to produce a report that looks
>something like this:
>
>Ethnicity | Under 25 | 26 thru 30 | 31 thru 40 | 41 & over | total | TOTAL
>===================================================================|======
> M | F M | F M | F M | F M | F
>1 1 1 0 0 0 0 0 0 1 1 2
>2 0 0 0 0 0 0 0 0 0 0 0
>3 1 0 0 0 0 0 0 0 1 0 1
>4 0 0 1 0 0 1 0 0 1 1 2
>N/A 0 1 0 0 0 0 0 0 0 1 1
>
>The decode statement is groups the data into each age bracker by sex and
>ethnicity. It does not do any of the totals, yet...I'd like it to, but can't
>figure out where to put the count statement or if I need one at all. The
>decode statement is:
>
><#---BEGIN DECODE STATEMENT----!>
>SELECT ethnic_origin,
> sum(decode( grouping, 'M1', 1, 0 )) M1,
> sum(decode( grouping, 'F1', 1, 0 )) F1,
> sum(decode( grouping, 'M2', 1, 0 )) M2,
> sum(decode( grouping, 'F2', 1, 0 )) F2,
> sum(decode( grouping, 'M3', 1, 0 )) M3,
> sum(decode( grouping, 'F3', 1, 0 )) F3,
> sum(decode( grouping, 'M4', 1, 0 )) M4,
> sum(decode( grouping, 'F4', 1, 0 )) F4
>FROM (
> select ethnic_origin, sex ||
> decode( sign(trunc(months_between(sysdate, birth_date)/12)-26), -1,
'1',
> decode( sign(trunc(months_between(sysdate, birth_date)/12)-31), -1,
'2',
> decode( sign(trunc(months_between(sysdate, birth_date)/12)-41), -1,
'3',
>'4' ) ) )
> grouping from current_students
>WHERE study_time = 'P'
>AND s_level = 'G')
> group by ethnic_origin ;
><#--End DECODE STATMENT--!>
>
>
>I get column totals by using the summary function in Reports Designer.
Is
>there a good way to get the Row totals without a seperate select statement?
>Can I add a field to the decode statement (another grouping?) that is a
>"total male of current ethnicity" and "total females of current ethnicity"
>for each row, then also a "total for this ethnicity" to get my row-wise sums?
>
>Thank you in advance for any help you can offer this oracle newbie.
>
>-Scott McCool

Hi Scott,
  I haven't tested this, but I think you can achieve what you want by doing the following:-
1. add sex to the list of columns returned by the in-line select (i.e. the one from current_students).
2. add the following to you main select statement :-   sum(decode(sex,'M',1,0)) MT,
  sum(decode(sex,'F',1,0)) FT,
  count(ethnic_origin) Total

If the count doesn't work try using sum(1) instead.

HTH,
  Rob Received on Mon Jun 29 1998 - 17:18:44 CDT