Results 1 to 3 of 3

Thread: Group By

  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Unanswered: Group By

    Hi,

    I've this result set..

    PONUMBER CURRENCY TOTAL
    ------------------------------ ---------- ----------
    PO001 RMB 120000
    PO002 RMB 460228
    PO003 PHP 14000


    how to turn results like this:

    PONUMBER CURRENCY TOTAL
    ------------------------------ ---------- ----------
    PO001 RMB 120000
    PO002 RMB 460228
    580228
    PO003 PHP 14000
    14000

    Group by CURRENCY and a SUBTOTAL by CURRENCY..

    Thx.

  2. #2
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333
    Hi,

    select ponumber
    , currency
    , total
    , sum(total) as SUM
    from <TABLE_NAME>
    group by currency
    SATHISH .

  3. #3
    Join Date
    Sep 2003
    Posts
    27

    Re: Group By

    If it's just for a report, use COMPUTE:

    break on currency;
    COMPUTE SUM LABEL "Sum" of total on currency;
    select ponumber, currency, total from <TABLE_NAME>;


    If you actually want to use the subtotal values then try an inline view:

    select t1.idnum,
    t1.currency,
    t1.total,
    inline_view.cur_total
    from <TABLE_NAME> t1,
    (select currency,
    sum(t2.total) as cur_total
    from <TABLE_NAME> t2
    group by currency) inline_view
    where t1.currency = inline_view.currency;

    Enjoy.

    Tim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •