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.
TechRickselect ITEM, DESCRIPT, MFR
, sum(QTY)
into newtable
from yourtable
group by ITEM, DESCRIPT, MFR

delete from yourtable

insert into yourtable
select * from temptable

rudy|||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|||sorry, i should have anticipated that

select ITEM, DESCRIPT, MFR
, sum(QTY) as QTY
into newtable
from yourtable
group by ITEM, DESCRIPT, MFR|||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|||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|||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|||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