Thanks Everyone I have what I was looking for for now. I will take on board your suggestions re. properly normalising the data and will probably end up with a setup similar tp Peters suggestions.
Thanks for now Tom -----Original Message----- From: ProfoxTech [mailto:[email protected]] On Behalf Of Ted Roche Sent: 20 April 2017 15:02 To: [email protected] Subject: Re: SQL sum On Thu, Apr 20, 2017 at 8:38 AM, Tom Dawson <[email protected]> wrote: > Hi All > > I have a stock table which records the locations and quantities of various > items. The layout of the table is like so: Tom: The problem with keeping a total in each row of the tables is that each record is now dependant on other records in the table. Each time you add or subtract a quantity from one row, you'll need to recalculate the rest. Also, if you add or delete a row, again, you'll need to do the re-total. This is "de-normalization" and leads to many problems and harder-to-maintain code. That said, if you want to do it, Here's setting up your data example: CREATE TABLE example (stockid C(5), location C(5) , quantity N(5), total n(5)) INSERT INTO example VALUES ("1","A",10,0) INSERT INTO example VALUES ("1","B",20,0) INSERT INTO example VALUES ("2","C",50,0) INSERT INTO example VALUES ("2","D",50,0) UPDATE example SET total=(select SUM(quantity) from example ex2 WHERE example.stockid=ex2.stockid) But don't do that :) -- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
