December 5, 2003 at 6:26 am
My table:
stock(id, item, price)
event(id, item_id, in, out)
fieldtypes:
id -> pk
item -> varchar
price -> money
id -> pk
item_id -> long integer (refers to stock.id)
in, out - > integer
I want a query which gives me a list of all the items in the stock and a total of the remainder of the stock for this item.
I got:
SELECT DISTINCT stock.id, stock.item, CAST(ISNULL
((SELECT SUM(in)
FROM event
WHERE event.item_id = stock.id), 0) AS int) AS total_in, CAST(ISNULL
((SELECT SUM(out)
FROM event
WHERE event.item_id = stock.id), 0) AS int) AS total_out
FROM stock LEFT OUTER JOIN
event ON stock.id = event.item_id
I have no problem with the totals for in and out (SUM() is doing that job), but how can I get the total of the remaining stock (total_in - total_out)?
in the select part I add
, total_in - total_out AS total_stock
But this gives an error 'invalid columnname' on both 'total_in' and 'total_out'
How can I get the grand total_stock?
I use Ms SQL server 2000
December 5, 2003 at 8:05 am
SELECT s.id, s.item,
SUM(ISNULL(e.in,0)) as 'total_in',
SUM(ISNULL(e.out,0)) as 'total_out',
SUM(ISNULL(e.in,0)-ISNULL(e.out,0)) as 'total_stock'
FROM stock s
LEFT OUTER JOIN event e
ON s.id = e.item_id
GROUP BY s.id, s.item
ORDER BY s.id, s.item
Edited by - davidburrows on 12/05/2003 08:05:24 AM
Far away is close at hand in the images of elsewhere.
Anon.
December 5, 2003 at 8:13 am
this is my subjestion
SELECT
stock.id,
stock.item,
COALESCE( SUM(in),0) AS total_in,
COALESCE( SUM(out),0) AS total_out,
COALESCE( SUM(in),0) - COALESCE( SUM(out),0) As total_stock
FROM
stock
LEFT OUTER JOIN
event
ON stock.id = event.item_id
GROUP BY
stock.id, stock.item
HTH
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply