The data found after adding a condition is not accurate! What's going on?

nitian

SELECT Stock.GoodsID, Goods.GoodsName,
 Goods.NormalPrice, SUM (Stock.Stock) AS Stock,
(SUM (Stock.Stock) * Goods.NormalPrice) AS allMoney
FROM Stock, Goods WHERE Stock.GoodsID = Goods.GoodsID
GROUP BY Stock.GoodsID, Goods.GoodsName, Goods.NormalPrice, Stock

The above is correct.I just add a condition and write:
SELECT Stock.GoodsID, Goods.GoodsName,
 Goods.NormalPrice, SUM (Stock.Stock) AS Stock,
(SUM (Stock.Stock) * Goods.NormalPrice) AS allMoney
FROM Stock, Goods WHERE Stock.GoodsID = Goods.GoodsID AND Stock <3
GROUP BY Stock.GoodsID, Goods.GoodsName, Goods.NormalPrice, Stock

After writing this, the inventory quantity found is wrong, what's going on ?!
The structure of the STOCK table is:
ID, GoodsID, WarehouseID, Stock

The above STOCK table may have the same GOODSID but different WAREHOUSEID, I think the problem may be here. For example:
ID GoodsID WarehouseID Stock
1 g1 w1 10
2 g1 w2 5

Thank you!

waxiaoning

SELECT Stock.GoodsID, Goods.GoodsName,
Goods.NormalPrice, SUM(Stock.Stock) AS Stock,
(SUM(Stock.Stock) * Goods.NormalPrice) AS allMoney
FROM Stock, Goods WHERE Stock.GoodsID = Goods.GoodsID GROUP BY Stock.GoodsID, Goods.GoodsName, Goods.NormalPrice, Stock
having sum( Stock) <3

waxiaoning

The total number of stocks is less than 3
Your method is a single record less than 3

fanfan1980

Different conditions will definitely give different results

pguppgdn

SELECT Stock.GoodsID, Goods.GoodsName,
Goods.NormalPrice, SUM(Stock.Stock) AS Stock,
(SUM(Stock.Stock) * Goods.NormalPrice) AS allMoney
FROM Stock, Goods WHERE Stock.GoodsID = Goods.GoodsID
GROUP BY Stock.GoodsID, Goods.GoodsName, Goods.NormalPrice, Stock
having SUM(Stock.Stock)>3

pguppgdn

Wrong writing is less than 3

mydriver

"The inventory quantity found after writing this way is wrong, what's going on?"

What does the correct data you want look like?

nitian

Thanks and learned again.