Re: SQL question!

From: Jurij Modic <jmodic_at_src.si>
Date: Thu, 22 Oct 1998 08:56:56 GMT
Message-ID: <362ef133.8543278@news.siol.net>

On Thu, 22 Oct 1998 06:18:30 GMT, violin.hsiao_at_mail.pouchen.com.tw (Violin) wrote:

>Hello,
>I have a SQL question.
>
>I have a table "PAY" and the colums:
>PAY_NO PAY_DATE VEN_NO PAY_AMT
>-----------------------------------------------------------------
>000001 19981001 A01 5000.00
>000002 19981002 A02 3500.50
>000003 19981005 A06 1820
>000004 19981006 A02 6300.25
> ::::::::::::::::::;
>I want to have a list order by sum(pay_amt) where pay_date >= '19981001'
>and <= '19981031' and show all rownum, like this:
>
>ROWNUM VEN_NO YYMM TOTAL
>----------------------------------------------------------
> 1 A02 199810 9800.75
> 2 A01 199810 5000.00
> 3 A06 199810 3000.50
> 4 :::::::::::::::::::::::
>
>So,I can know in '199810',No.1 is 'A02' and sum(pay_amt) is 9800.75,
>No.2 is 'A02' and total is 5000..........
>
>Maybe you know how to create the SELECT statement.

Here is one of possible SQL sollutions (it will work with Oracle 7.2 and above):

SQL> SELECT * FROM pay;

PAYNO PAY_DATE VEN PAY_AMT
--------- -------- --- ---------

        1 19981001 A01      5000
        2 19981002 A02    3500.5
        3 19981005 A06      1820
        4 19981006 A02   6300.25

SQL> SELECT rownum, y.ven_no, y.yymm, -y.total total FROM 2 (SELECT MIN(x.ven_no) ven_no, x.yymm, -x.total total FROM

  3      (SELECT ven_no, SUBSTR(pay_date,1,6) yymm, SUM(pay_amt) total
  4       FROM pay
  5       WHERE pay_date BETWEEN '19981001' AND '19981031'
  6       GROUP BY ven_no, SUBSTR(pay_date,1,6)) x
  7     GROUP BY -x.total, x.yymm) y

8 /

ROWNUM VEN YYMM TOTAL
--------- --- ------ ---------

        1 A02 199810   9800.75
        2 A01 199810      5000
        3 A06 199810      1820

SQL>
>I'll appreciate for your help.
>Thank you in advance.
>Please Cc to: violin.hsiao_at_mail.pouchen.com.tw
>
>Violin.

HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

The above opinions are mine and do not represent any official standpoints of my employer Received on Thu Oct 22 1998 - 03:56:56 CDT