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

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/

Thanks for the help. I was able to get it worked out with your suggestions.

Best Regards,
TechRick