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.