Re: SQL help needed

From: Jonathan Gennick <gennick_at_worldnet.att.net>
Date: 1997/11/03
Message-ID: <34663b56.8574329@netnews.worldnet.att.net>#1/1

On Sun, 02 Nov 1997 06:27:11 -0500, "Michael G. Schneider" <mgs_software_at_compuserve.com> wrote:

>Given the following table (customers with payment on a certain day)
>
> CREATE TABLE tbl
> (
> CustNo NUMBER,
> Day DATE,
> Amount NUMBER
> )
>
>Is it then possible to create a view, which shows ...
>
> - vertically: the customers
> - horizontal: the sum of the amounts for one month
>

Sure! See below. The final view is what you want, but you can see how I worked up to it.

SQL> describe tbl;

 Name                            Null?    Type
 ------------------------------- -------- ----
 CUSTNO                                   NUMBER
 DAY                                      DATE
 AMOUNT                                   NUMBER

SQL>
SQL> select * from tbl;

        1 01-NOV-97       100
        1 01-NOV-97       200
        1 01-DEC-97       300
        1 01-DEC-97       100

SQL>
SQL> create or replace view tbl_by_mon as   2 select CustNo,
  3 decode (to_char(Day,'mon'),'nov',Amount,null) nov_amt,
  4 decode (to_char(Day,'mon'),'dec',Amount,null) dec_amt
  5 from tbl;

View created.

SQL>
SQL> select * from tbl_by_mon;

        1       100
        1       200
        1                 300
        1                 100

SQL>
SQL> create or replace view sum_by_mon as   2 select CustNo,
  3 sum(decode
(to_char(Day,'mon'),'nov',Amount,null)) nov_sum,   4 sum(decode
(to_char(Day,'mon'),'dec',Amount,null)) dec_sum

  5      from tbl
  6      group by CustNo;

View created.

SQL>
SQL> select * from sum_by_mon;

        1 300 400

regards,

Jonathan Gennick

---

gennick_at_worldnet.att.net
http://home.att.net/~gennick Received on Mon Nov 03 1997 - 00:00:00 CST