Re: Crosstab with Oracle SqlPlus?

From: <antonyliu2002_at_yahoo.com>
Date: 25 Aug 2006 20:18:41 -0700
Message-ID: <1156562321.234252.263400@75g2000cwc.googlegroups.com>

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. Received on Fri Aug 25 2006 - 22:18:41 CDT