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

    Question Unanswered: Consolidating Rows and Summing Totals

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

    For example:

    ITEM DESCRIPT MFR QTY
    ----- ----------- ---- ----
    ABC TABLE OSH 1
    ABC TABLE OSH 3
    ABC TABLE OSH 2
    ABD CHAIR KMT 2
    ABD CHAIR KMT 1
    ABE PILLOW SOF 1


    I would like to display this information as:

    ITEM DESCRIPT MFR QTY
    ----- ----------- ---- ----
    ABC TABLE OSH 6
    ABD CHAIR KMT 3
    ABE PILLOW SOF 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
    select ITEM, DESCRIPT, MFR
    , sum(QTY)
    into newtable
    from yourtable
    group by ITEM, DESCRIPT, MFR

    delete from yourtable

    insert into yourtable
    select * from temptable


    rudy

  3. #3
    Join Date
    Aug 2002
    Location
    Southern California
    Posts
    35
    Thanks Rudy,

    I just tried it but I lose my column header when I use the SUM.
    Can I select by column number?

    This is the error I recieved:
    Server: Msg 8155, Level 16, State 1, Line 70
    No column was specified for column 4 of 'temptable'

    Thanks again,
    TechRick

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i should have anticipated that

    select ITEM, DESCRIPT, MFR
    , sum(QTY) as QTY
    into newtable
    from yourtable
    group by ITEM, DESCRIPT, MFR

  5. #5
    Join Date
    Aug 2002
    Location
    Southern California
    Posts
    35
    Originally posted by r937
    sorry, i should have anticipated that

    select ITEM, DESCRIPT, MFR
    , sum(QTY) as QTY
    into newtable
    from yourtable
    group by ITEM, DESCRIPT, MFR
    Rudy,

    I've tried repeatedly and in various ways to implement your suggestion and for some reason the rows are not being consolidated. The same information is in both tables.

    Is is possible that I need to do a loop and then consolidate like items?

    TechRick

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pick a couple of items, let's call them 'itm1' and 'itm2', and run a detail report

    select ITEM, DESCRIPT, MFR, QTY
    from yourtable
    where ITEM in ('itm1','itm2')
    order by ITEM, DESCRIPT, MFR

    post the results (assuming only a few lines, eh)

    now run a summary report

    select ITEM, DESCRIPT, MFR, sum(QTY) as QTY
    from yourtable
    where ITEM in ('itm1','itm2')
    group by ITEM, DESCRIPT, MFR

    and post the results

  7. #7
    Join Date
    Aug 2002
    Location
    Southern California
    Posts
    35
    Originally posted by r937
    pick a couple of items, let's call them 'itm1' and 'itm2', and run a detail report

    select ITEM, DESCRIPT, MFR, QTY
    from yourtable
    where ITEM in ('itm1','itm2')
    order by ITEM, DESCRIPT, MFR

    post the results (assuming only a few lines, eh)

    now run a summary report

    select ITEM, DESCRIPT, MFR, sum(QTY) as QTY
    from yourtable
    where ITEM in ('itm1','itm2')
    group by ITEM, DESCRIPT, MFR

    and post the results
    Here's what I did. It seems to work.

    CODE:
    select mfr_sku, title, MFR_name, Q_stk
    from A1_TEMPTABLE
    where mfr_sku = 'st19171wc'
    order by mfr_sku, title, MFR_name

    select mfr_sku, title, MFR_name, sum(Q_stk) as Q_stk
    from A1_TEMPTABLE
    where mfr_sku = 'item1'
    group by mfr_sku, title, MFR_name

    RESULTS:

    ITEM1 9GB 3.5 80PIN SCSI SEA 486
    ITEM1 9GB 3.5 80PIN SCSI SEA 431


    ITEM1 9GB 3.5 80PIN SCSI SEA 941

    I think I might know what I was doing wrong but I'll have to check it to make sure...

    Thanks again,
    Rick

  8. #8
    Join Date
    Aug 2002
    Location
    Southern California
    Posts
    35

    Cool

    Originally posted by r937
    sorry, i should have anticipated that

    select ITEM, DESCRIPT, MFR
    , sum(QTY) as QTY
    into newtable
    from yourtable
    group by ITEM, DESCRIPT, MFR
    Sure enough, it was as I thought. I was adding the QTY in the 'group by' statement and that seems to have been causing the problem. I thought that it was required but it wasn't. It's all worked out now -- working like a charm.

    Thanks Rudy!

    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
  •