Re: enumerate domain of column as new columns.. count

From: Ed Prochak <edprochak_at_magicinterface.com>
Date: Tue, 09 Jul 2002 19:37:04 GMT
Message-ID: <3D2B6803.8A229EA2@magicinterface.com>

Okay, I'll spell it out for you. He has a fixed number of columns,  so set up a decode for each one.

SELECT app,

       count(decode(apptype,'A','X',NULL) as A,
       count(decode(apptype,'B','X',NULL) as B,
       count(decode(apptype,'C','X',NULL) as C,
       count(decode(apptype,'A','X','B','X','C','X',NULL) as Total

FROM table1 group by app ;

Note I had all the decodes return 'X' to emphsize that count()  doesn't care what the value is, as long as it is NOT NULL.  With a little practice even a lowly intern can do it. Of course,  the whole query needs to be touched if another APPTYPE is needed  in the report. But this is the simple solution for the simple case.

 (Tom Kyte has shown this may times, check his web site. Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities)

Final note: with version 8 and above, you may be able to use CASE WHEN

TurkBear wrote:
>
> Ed, A multi-word question:
> How can DECODE produce a cross-tab?
>
> Ed Prochak <edprochak_at_magicinterface.com> wrote:
>
> >
> >This is a relatively simple case, so I have a one word suggestions for
> >you: DECODE()
> >
> >(IOW, check out using the decode function.)
> >
> > ed
> >
> >tone wrote:
> >>
> >> hey everyone.. i cant figure out the answer to this. Id like to do it
> >> in SQL/PLSQL if it can be done. ie. i would rather not have to mess
> >> with java..
> >> this is the result of a select statement (select app, apptype,
> >> count(*) from table1 group by app, apptype;)
> >>
> >> app type count
> >> app1 A 3
> >> app1 B 2
> >> app2 A 1
> >> app2 C 2
> >> ...
> >>
> >> im trying to get it to look like this
> >>
> >> app A B C Total
> >> app1 3 2 0 5
> >> app2 1 0 2 3
> >>
> >> and for the life of me, i cant figure it out.. i cant even describe
> >> the problem in db terms.
> >>
> >> can someone, anyone help me.. ???
> >> -confused intern at big company..
> >> ps. i have to go fetch the coffee now. the life of an intern. ya gotta
> >> love it!

-- 
Edward J. Prochak   --- Magic Interface, Ltd.
Ofc: 440-498-3700
on the web at       --- http://www.magicinterface.com
email: ed.prochak_at_magicinterface.com

Received on Tue Jul 09 2002 - 14:37:04 CDT