Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Crosstab with Oracle SqlPlus?

Re: Crosstab with Oracle SqlPlus?

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: Sun, 27 Aug 2006 14:56:21 +0000 (UTC)
Message-ID: <ecsbql$if8$1@klatschtante.init7.net>


On 2006-08-26, antonyliu2002_at_yahoo.com <antonyliu2002_at_yahoo.com> wrote:

> 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.

The problem is that your alias starts with a digit instead of a non-digit character:

Alias starts with digit:

    RENE> select count(*) 1u from dual;
    select count(*) 1u from dual

                    *

    ERROR at line 1:
    ORA-00923: FROM keyword not found where expected

Alias starts with non digit:

    RENE> select count(*) u1 from dual;     

            U1


             1

Alias in "quoted":

    RENE> select count(*) "1u" from dual;     

            1u


             1

hth,
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/
Received on Sun Aug 27 2006 - 09:56:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US