More complex decode help needed

From: <ddreams_at_my-dejanews.com>
Date: Mon, 29 Jun 1998 20:06:14 GMT
Message-ID: <6n8s3m$tns$1@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

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Mon Jun 29 1998 - 15:06:14 CDT