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]