Re: Help Wanted: How to write a single select clause for mutiple counts

From: Thomas J Kyte <tkyte_at_us.oracle.com>
Date: 1996/04/01
Message-ID: <4jpl7q$1an_at_inet-nntp-gw-1.us.oracle.com>#1/1

jiang_at_ccs.neu.edu (Hong Jiang) wrote:

>Hi,

>There is a table like this:
 

>--------------------------------------------------------------------------
>Student_ID Register Day
 

>0124 1-OCT-95
>0342 2-OCT-95
>3213 11-NOV-95
>4466 30-NOV-95
>0990 14-NOV-95
>0345 2-SEP-95
 
>Users want to know how many students registed in Oct, How many in Nov and so
>on.
 

>I used UNION ALL to combine the rows returned by several SELECTs each of which
>count a tatol number for ecah month. The result look like:
 

>2
>3
>1
 

>But It isn't What I want.
 

>Can I write a single select clause to get the result in one row.
>The result should looks like
 

>OCT NOV SEP
>2 3 1

select sum( decode(to_char(register_day,'MON'),'JAN',1,0) ) 'JAN',
       sum( decode(to_char(register_day,'MON'),'FEB',1,0) ) 'FEB',
       sum( decode(to_char(register_day,'MON'),'MAR',1,0) ) 'MAR',
       sum( decode(to_char(register_day,'MON'),'APR',1,0) ) 'APR',
        .........
       sum( decode(to_char(register_day,'MON'),'DEC',1,0) ) 'DEC'

from T
/

>Do you think it is posible?

Absolutely.

>Thank you very much!!!
 

>Jiang Honng

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government

---

opinions and statements are mine and do not necessarily reflect the opinions of Oracle Corporation. Received on Mon Apr 01 1996 - 00:00:00 CEST