I apologize in advance for my English.
I do IT project database. I created a sample database wine warehouse. But when I try to calculate the profit and condition of the goods, the program displays wrong results.
You can buy goods on one ID several times, sell too.
I'm trying to do it with three tables: Buy, Sell, Commodity.
Buy: Id_Buy | Id_commodity | Amount | Price
Sell: Id_Sell | Id_Commodity | Amount | Price
Commodity: Id_Commodity | Name
In another table I'm trying to do:
Name | Sum of one ID : Amount.Buy | Sum of one ID: Amount.Sell | The number of boxes which stay in stock (Amonut.Buy - Amount.Sell) | Profit (Price.Sell - Price.Buy)
Profit I think it's allright but I have no idea how program is summing amount.buy and amount.sell because it looks like when it's trying to sum of one ID amount.buy it's check how many times it was sold and then It multiplies it to the amount bought (yes, it's hard to understand when someone has no idea how to say it in english)
My code from the fourth table:
Code:
SELECT Commodity.Name , Sum(Buy.Amount) AS Amount.Buy, Sum(Sell.Amount) AS Amount.Sell, (Sum(Buy.Amount)-Sum(Sell.Amount)) AS The number of boxes.., (Sum(Sell.Price)-Sum(Buy.Price)) AS Profit
FROM (Buy INNER JOIN Commodity ON Buy.ID_Commodity = Commodity.ID_Commodity) INNER JOIN Sell ON Buy.ID_commodity = Sell.Id_commodity
WHERE (((Commodity.Id_commodity) = Buy.Id_Commodity AND (Commodity.Id_commodity) = Sell.Id_commodity))
GROUP BY Commodity.Name;
I don't know why program add "INNER JOIN" to this code also.
If anything is unclear, please ask in the comments.