RE: SQL sum

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:profoxtech-bounces@leafe.com] On Behalf Of Ted Roche Sent: 20 April 2017 15:02 To: profoxtech@leafe.com Subject: Re: SQL sum

On Thu, Apr 20, 2017 at 8:38 AM, Tom Dawson TDawson@flexipol.co.uk 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]