Re: Crosstab with Oracle SqlPlus?

antonyliu2002_at_yahoo.com wrote:
> DA Morgan wrote:
> > antonyliu2002_at_yahoo.com wrote:
> > > OK, I know this has been asked many times. I've done a google search,
> > > but but not figure out.
> > >
> > > I have a very simple table of student information in a Oracle database
> > > like below, where STU_ID is student id and COLL_CD is college code and
> > > CLS_LEVEL is class level.
> > >
> > > STU_ID COLL_CD CLS_LEVEL
> > > 123 KV 1U
> > > 234 KV 1U
> > > 345 KY 1U
> > > 456 KN 1G
> > > 567 KP 1L
> > > 678 KV 1U
> > > 789 KY 1G
> > > 890 KY 1G
> > > 901 KY 1U
> > > 911 KP 1V
> > > 912 KV 1L
> > > 913 KN 1G
> > > 915 KP 1U
> > >
> > > I would like to count and list the number of students of each class
> > > level in each college like so:
> > >
> > > COLL_CD 1U 1G 1L 1V
> > > KV 23 2 0 0
> > > KY 9 16 88 1
> > > KN 13 45 55 0
> > > KP 0 0 61 18
> > >
> > > I know I can do
> > >
> > > Select count(*) from T_STUDENT where COLL_CD='KV' and CLS_LEVEL='1U'
> > >
> > > one after another.
> > >
> > > But can I get this crosstab in a single SQL query with SqlPlus?
> > >
> > > Thanks.
> >
> > Morgan's library at www.psoug.org
> > click on DECODE.
> > --
> > Daniel A. Morgan
> > University of Washington
> > damorgan_at_x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Group
> > www.psoug.org
>
> Hi, Morgan,
>
> I found your notes at http://www.psoug.org/reference/decode_case.html
> and took your example SQL as below:
>
> SELECT program_id,
> COUNT (AMERICAN) AAL,
> COUNT (DELTA) DAL,
> COUNT (NORTHWEST) NWO,
> COUNT(INTL_LEASING) ILC
> FROM (
> SELECT program_id,
> DECODE(customer_id, 'AAL', 'AAL') AMERICAN,
> DECODE(customer_id, 'DAL', 'DAL') DELTA,
> DECODE(customer_id, 'NWO', 'NWO') NORTHWEST,
> DECODE(customer_id, 'ILC', 'ILC') INTL_LEASING
> FROM airplanes)
> GROUP BY program_id;
>
> I created the SQL query by following this example as shown below:
>
> SELECT
> COLL_CD,
> COUNT(UNDER) 1U,
> COUNT(GRAD) 1G,
> COUNT(VET) 1V,
> COUNT(LAW) 1L
> FROM (
> SELECT COLL_CD,
> DECODE(CLS_LEVEL, '1U', '1U') UNDER,
> DECODE(CLS_LEVEL, '1G', '1G') GRAD,
> DECODE(CLS_LEVEL, '1V', '1V') VET,
> DECODE(CLS_LEVEL, '1L', '1L') LAW
> FROM student)
> GROUP BY COLL_CD;
>
> But I get the following error:
>
> COUNT(UNDER) 1U,
> *
> ERROR at line 3:
> ORA-00923: FROM keyword not found where expected
>
> I am using Oracle 10i with SQLPLUS.

It turns out that my sql*plus does not accept alias for the count function. In other words, if I replace the following lines

COLL_CD,

COUNT(UNDER) 1U,
COUNT(GRAD) 1G,
COUNT(VET) 1V,
COUNT(LAW) 1L

with

COLL_CD,

COUNT(UNDER),
COUNT(GRAD),
COUNT(VET),
COUNT(LAW)

It works perfect.

I am not sure how to assign an alias to the count function. Received on Sat Aug 26 2006 - 15:08:03 CDT