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.

antonyliu2002@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@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

DA Morgan wrote:
> antonyliu2002@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@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.

antonyliu2002@yahoo.com wrote:
> DA Morgan wrote:
> > antonyliu2002@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@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.

On 2006-08-26, antonyliu2002@yahoo.com <antonyliu2002@yahoo.com> wrote:
> antonyliu2002@yahoo.com wrote:
>> DA Morgan wrote:
>> > antonyliu2002@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@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/

Rene Nyffenegger wrote:
> On 2006-08-26, antonyliu2002@yahoo.com <antonyliu2002@yahoo.com> wrote:
> > antonyliu2002@yahoo.com wrote:
> >> DA Morgan wrote:
> >> > antonyliu2002@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@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/

Thanks, exactly that is the case.

You know, I did suspect that the problem was that my aliases started
with a numeric number, so I did try quoting them with SINGLE quotation,
which, as it turned out, does not work, and DOUBLE quotation worked!

So, why is that we use single quotation in the DECODE function as
below?

DECODE(CLS_LEVEL, '1U', '1U') UNDER,

And it looks like double quotation in such functions won't work.

I guess I am asking about the differences of single quotation and
double quotation in sql*plus.

antonyliu2002@yahoo.com wrote:
> Rene Nyffenegger wrote:
> > On 2006-08-26, antonyliu2002@yahoo.com <antonyliu2002@yahoo.com> wrote:
> > > antonyliu2002@yahoo.com wrote:
> > >> DA Morgan wrote:
> > >> > antonyliu2002@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@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/
>
> Thanks, exactly that is the case.
>
> You know, I did suspect that the problem was that my aliases started
> with a numeric number, so I did try quoting them with SINGLE quotation,
> which, as it turned out, does not work, and DOUBLE quotation worked!
>
> So, why is that we use single quotation in the DECODE function as
> below?
>
> DECODE(CLS_LEVEL, '1U', '1U') UNDER,
>
> And it looks like double quotation in such functions won't work.
>
> I guess I am asking about the differences of single quotation and
> double quotation in sql*plus.

The reason is that within DECODE call '1U' is a *literal value* (a
string,) whereas in the query column list it's an *identifier*. Strings
are enclosed with single quotes. In Oracle, identifiers are
conventionally all uppercase and can't start with digits (if you use
mixed or lower case, it will be automatically converted to upper case.)
However, if you enclose arbitrary string with double quotes, it is
accepted as an identifier as is, with case preserved and any characters
forming the name. So Oracle will not accept 1U, but will accept "1U" as
an alias.

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

Another option is Oracle's Discoverer has this feature built into it
and its easy to design

George

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

Hey, George,

Do you mind giving me more info about Oracle Discoverer?

My questions are here:

1. Is it possible to install Discoverer on computer A and hook it up
with a data source on computer B?

2. Is it drag-and-drop kind of thing like Business Object?

If your answer to both questions is 'Yes', then that might be the kind
of reporting tool my boss is looking for.

Thanks.


George Lewycky wrote:
> Another option is Oracle's Discoverer has this feature built into it
> and its easy to design
>
> George
>
> antonyliu2002@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.

Great. Thank you very much. That makes sense. Never realized that
Oracle makes such a distinction about these 2 types of quotation marks.
(How did we end up getting two types of quotation marks anyway?)

Vladimir M. Zakharychev wrote:
> antonyliu2002@yahoo.com wrote:
> > Rene Nyffenegger wrote:
> > > On 2006-08-26, antonyliu2002@yahoo.com <antonyliu2002@yahoo.com> wrote:
> > > > antonyliu2002@yahoo.com wrote:
> > > >> DA Morgan wrote:
> > > >> > antonyliu2002@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@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/
> >
> > Thanks, exactly that is the case.
> >
> > You know, I did suspect that the problem was that my aliases started
> > with a numeric number, so I did try quoting them with SINGLE quotation,
> > which, as it turned out, does not work, and DOUBLE quotation worked!
> >
> > So, why is that we use single quotation in the DECODE function as
> > below?
> >
> > DECODE(CLS_LEVEL, '1U', '1U') UNDER,
> >
> > And it looks like double quotation in such functions won't work.
> >
> > I guess I am asking about the differences of single quotation and
> > double quotation in sql*plus.
>
> The reason is that within DECODE call '1U' is a *literal value* (a
> string,) whereas in the query column list it's an *identifier*. Strings
> are enclosed with single quotes. In Oracle, identifiers are
> conventionally all uppercase and can't start with digits (if you use
> mixed or lower case, it will be automatically converted to upper case.)
> However, if you enclose arbitrary string with double quotes, it is
> accepted as an identifier as is, with case preserved and any characters
> forming the name. So Oracle will not accept 1U, but will accept "1U" as
> an alias.
>
> Regards,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com