1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Select with group by

Discussion in 'SQL PL/SQL' started by akika, Jan 26, 2018.

  1. akika

    akika Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    hi,

    How can I amend this select to make a sum & group by a single line....

    SELECT article_ref, status, total_amount
    FROM order_tbl
    GROUP BY article_ref, status, total_amount
    ORDER BY article_ref, status, total_amount;

    tbl_order
    article_ref status total_amount
    123456 Ordered 100
    123456 Ordered 10
    123456 Ordered 80
    123456 Pending 50
    123456 Pending 23
    123456 Pending 50
    123456 Delivered 50
    123456 Delivered 75




    And want the output to be
    123456 Ordered 190
    123456 Pending 123
    123456 Delivered 125
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,616
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You have no aggregate in your select list so I'm not surprised you're not getting the results you want; this should give you what you're asking for:

    Code (SQL):
    SQL> SELECT article_ref, STATUS, SUM(total_amount) total_amount
      2  FROM order_tbl
      3  GROUP BY article_ref, STATUS
      4  ORDER BY article_ref, STATUS, total_amount;

    ARTICLE_REF STATUS       TOTAL_AMOUNT
    ----------- ------------ ------------
         123456 Delivered             125
         123456 Ordered               190
         123456 Pending               123