Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Location
    Southern California
    Posts
    35

    Unanswered: Consolidating Like Items and Summing Totals

    I need to know if there is an easy way to consolidate lines of the same item but differing quantities.

    For example:

    ITEM QTY
    ----- ----
    ABC 1
    ABC 3
    ABC 6
    ABD 2
    ABD 1
    ABE 1


    I would like to display this information as:

    ITEM QTY
    ----- ----
    ABC 10
    ABD 3
    ABE 1

    Summary: I want to consolidate those items which appear in the table more than once by combining their quantities and leaving one row that has the sum of all.

    Your help is greatly appreciated.
    TechRick

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    selecting the totals is easy, right?

    select ITEM, SUM(QTY)
    from yourtable
    group by ITEM

    however, consolidating them and "leaving one row" is tricky

    i suggest writing the total rows to a temporary table, deleting all rows from the original, then inserting the total rows back

    create table temptable
    ( ITEM char(3)
    , QTY integer )

    insert into temptable
    select ITEM, SUM(QTY)
    from yourtable
    group by ITEM

    delete from your table

    insert into yourtable
    select * from temptable

    drop temptable


    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Aug 2002
    Location
    Southern California
    Posts
    35
    Thanks for the help. I was able to get it worked out with your suggestions.

    Best Regards,
    TechRick

Posting Permissions

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