Re: Crosstab Query

From: Chrysalis <cellis_at_clubi.ie>
Date: Thu, 01 Oct 1998 22:06:18 +0100
Message-ID: <cellis-ya02408000R0110982206180001@news.clubi.ie>

In article <6unf1s$ktc$1_at_nnrp1.dejanews.com>, speng_at_ncs.com.sg wrote:

> I have a table with the columns as shown below:
>
> <My_table>
> unemp varchar2(1)
> isdereg varchar2(1)
> exp date
> rereg_dte date
> renel_dte date
>
> How to i select a crosstab query that will calculate total no. in NewReg where
> unemp='0' and so on.
>
> unemp NewReg Re_Reg Renew_Reg
> 0 total total total
> 1
>
> where the conditon for:
> NewReg => isdereg ='N' and exp > sysdate
> rereg_dte and renl_dte is null
>
> Re_reg => isdereg='N' and exp > sysdate
> rereg_dte is not null
>
> Renew_reg => isdereg='N' and exp > sysdate
> renl_dte is not null
>
>
> --
> Sok Peng

Generally, problems requiring counts of rows with differing conditions in a single output row are attacked using sum(decode(...)):

select unemp

      ,sum(decode(rereg_dte,null,decode(renel_dte,null,1,null),null)) "Newreg"
      ,sum(decode(rereg_dte,null,null,1)) "Re_Reg"    -- Note (1)
      ,sum(decode(renel_dte,null,null,1)) "Renew_Reg" -- Note (1)

from <my_table>
where isdereg = 'N' -- Note (2)
 and exp > sysdate -- Note (2,3)
group by unemp
/

Notes:
(1) Counts for "Re_Reg" and "Renew_Reg" will overlap if both rereg_dte and renel_dte can be non-null. Is this what you want?

(2) Common conditions (avoid summing unwanted rows)

(3) You may wish to use trunc(sysdate) here to avoid different results at different times of the day!

HTH
Chrysalis
--
FABRICATE DIEM PVNC
("To Protect and to Serve")
Motto of the Night Watch
Terry Pratchett - "Guards, Guards" Received on Thu Oct 01 1998 - 16:06:18 CDT