Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 5 of 5 total
Thread Grouping Quantities via SQL
Tue, Oct 7 2008 3:29 AMPermanent Link

Chris Holland

SEC Solutions Ltd.

Avatar

Team Elevate Team Elevate

Hi,

I have a table "Orders" that contains items with a quantity e.g.

Qty.      CatNo      Description
===============================================
1   K2747      2 Gang Switched Socket
2   862ZIC      35mm Socket Box
3   K2747      2 Gang Switched Socket
4   862ZIC      35mm Socket Box

I would like to total up the quantities and end up with a total for each
item like this.

Qty.      CatNo      Description
===============================================
4   K2747      2 Gang Switched Socket
6   862ZIC      35mm Socket Box

What is the best way to achive this using SQL and end up with totaled
items in the same table as I started i.e. "Orders"?

Chris HOlland
Tue, Oct 7 2008 3:37 AMPermanent Link

Chris Holland

SEC Solutions Ltd.

Avatar

Team Elevate Team Elevate

This is the best way that I can find.

SELECT SUM(Qty) AS Qty,CatNo,Description INTO MEMORY Temp FROM Orders
GROUP BY Item;
SELECT * INTO Orders FROM MEMORY TEMP

Using DBISAM 3 by the way.

Chris HOlland
Tue, Oct 7 2008 9:45 AMPermanent Link

"Robert"

"Chris Holland [Team Elevate]" <mail@chrisholland.me.uk> wrote in message
news:790F1B14-FC7A-439A-B5FA-36F0B6CD4315@news.elevatesoft.com...
> This is the best way that I can find.
>
> SELECT SUM(Qty) AS Qty,CatNo,Description INTO MEMORY Temp FROM Orders
> GROUP BY Item;
delete from orders; <<<<<<
insert into orders  <<<<<<
> SELECT *  FROM MEMORY TEMP
>

Robert

Tue, Oct 7 2008 2:24 PMPermanent Link

Chris Holland

SEC Solutions Ltd.

Avatar

Team Elevate Team Elevate

Thanks Robert,

That works better because my way lost the indices on the original table.

Chris

Robert wrote:
> "Chris Holland [Team Elevate]" <mail@chrisholland.me.uk> wrote in message
> news:790F1B14-FC7A-439A-B5FA-36F0B6CD4315@news.elevatesoft.com...
>> This is the best way that I can find.
>>
>> SELECT SUM(Qty) AS Qty,CatNo,Description INTO MEMORY Temp FROM Orders
>> GROUP BY Item;
> delete from orders; <<<<<<
> insert into orders  <<<<<<
>> SELECT *  FROM MEMORY TEMP
>>
>
> Robert
>
>
Tue, Oct 7 2008 2:35 PMPermanent Link

"Robert"

"Chris Holland [Team Elevate]" <mail@chrisholland.me.uk> wrote in message
news:59E5B4CA-CDC8-4735-862A-CE5CC52E92E8@news.elevatesoft.com...
> Thanks Robert,
>
> That works better because my way lost the indices on the original table.
>

I forgot you can do EMPTY TABLE Orders instead of deleting records. Probably
much faster.

Robert


Image