Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 5 of 5 total |
Grouping Quantities via SQL |
Tue, Oct 7 2008 3:29 AM | Permanent Link |
Chris Holland SEC Solutions Ltd. 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 AM | Permanent Link |
Chris Holland SEC Solutions Ltd. 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 AM | Permanent 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 PM | Permanent Link |
Chris Holland SEC Solutions Ltd. 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 PM | Permanent 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 |
This web page was last updated on Friday, March 29, 2024 at 03:30 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |