Hi All
I have a stock table which records the locations and quantities of various items. The layout of the table is like so:
|stock id| |location| |quantity|
I want to be able to add a total field into the table which is a total of each unique stock id.
To do this I tried to use a sum(quantity) followed by a group by. The problem is grouping by id ignores the distinct location info. For example
stock id
location
quantity
1
A
10
1
B
20
2
C
50
2
D
50
Becomes:
stock id
location
quantity
Total
1
A
10
30
2
C
20
100
Although the totals are right the group by has cause me to lose my distinct location info, what I really want is
stock id
location
quantity
Total
1
A
10
30
1
B
20
30
2
C
50
100
2
D
50
100
I'm sure it's relatively but I'm obvious missing something.
Thanks in advance Tom
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
Are you grouping by stock_id AND location?
Laurie
On 20 April 2017 at 13:38, 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:
|stock id| |location| |quantity|
I want to be able to add a total field into the table which is a total of each unique stock id.
To do this I tried to use a sum(quantity) followed by a group by. The problem is grouping by id ignores the distinct location info. For example
stock id
location
quantity
1
A
10
1
B
20
2
C
50
2
D
50
Becomes:
stock id
location
quantity
Total
1
A
10
30
2
C
20
100
Although the totals are right the group by has cause me to lose my distinct location info, what I really want is
stock id
location
quantity
Total
1
A
10
30
1
B
20
30
2
C
50
100
2
D
50
100
I'm sure it's relatively but I'm obvious missing something.
Thanks in advance Tom
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
Hi Tom,
what do you want to have as a result if your datas are 1 A 10 1 B 20 1 A 30
If you want 1 A 40 1 B 20 you can write SELECT stock_id, location, SUM(quantity) as mysum FROM mytable GROUP BY stock_id, location INTO .... but in this case (and except with VFP) you can't have a 'quantity' field in the result.
If you want as a result 1 A 10 40 1 B 20 20 1 A 30 40 (so in each line of your table you have the total number of the current id in the location) you can write SELECT stock_id, location, quantity, (SELECT SUM(quantity) FROM mytable s WHERE m.stock_id = s.stock_id AND m.location = s.location) as mysum FROM mytable m INTO ....
Note : I haven't check syntax so may be you must adjust the command !
Is this help ? The foxil
On 2017-04-20 09:43, Jean MAURICE wrote:
BIG ERROR !
you MUST NOT have m as local alias. Choose another letter !!
The Foxil
That's another reason why I never use single-letter aliases. Learned that tip from Ed Leafe last century, as I recall, when I was first working in VFP5/6. I always use at least 2, and it's usually f1, rpt, etc.
Hi Tom,
Not sure if you are mixing up different tables here. In most of the stock systems I have seen you have: Stock header file. [stock id] [quantity] (in stock, allocated, on PO etc) This total could be say 100 in 4 locations, so 4 records in location file.
then you have a stock location file
[Stock id] [location] [quantity] (of that stock id in that location)
So the stock header file contains an instock total and the location file is a detail file which will have a record for each location for each stock item.
HTH
Peter
On 20/04/2017 13:38, Tom Dawson 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:
|stock id| |location| |quantity|
I want to be able to add a total field into the table which is a total of each unique stock id.
To do this I tried to use a sum(quantity) followed by a group by. The problem is grouping by id ignores the distinct location info. For example
stock id
location
quantity
1
A
10
1
B
20
2
C
50
2
D
50
Becomes:
stock id
location
quantity
Total
1
A
10
30
2
C
20
100
Although the totals are right the group by has cause me to lose my distinct location info, what I really want is
stock id
location
quantity
Total
1
A
10
30
1
B
20
30
2
C
50
100
2
D
50
100
I'm sure it's relatively but I'm obvious missing something.
Thanks in advance Tom
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
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 :)
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]
-
Gérard LOCHON -
Jean MAURICE -
Laurie Alvey -
mbsoftwaresolutions＠mbsoftwaresolutions.com -
Peter Cushing -
Ted Roche -
Tom Dawson