Are you grouping by stock_id AND location? Laurie
On 20 April 2017 at 13:38, 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: > > |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] _______________________________________________ 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/camvtr9fu7sefvv2ebw+9ksje2v9wdti0w-wk6-z80mnq3rp...@mail.gmail.com ** 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.
