Group By not working to calculate amount left in MySql

Question

I am trying to calculate the amount of stock left after transactions have been made, however it is returning me a negative amount. What could I be doing wrong with my GROUP BY as it is not seeming to be working or is it the actual calculation (I am a complete beginner as you can tell)?

    CREATE TABLE items (
    amount int,
    price_for_each int,
    name VARCHAR(20),
    CONSTRAINT PK_items PRIMARY KEY (name)
);
INSERT INTO items (price_for_each, amount, name) VALUES 
(110,22,'2lof milk'),
(99,30,'6 cans of lemonade'),
(150,20,'Pack of butter'),
(450,13,'Roast chicken'),
(99,30,'Pack of rice'),
(20,50,'Banana'),
(200,30,'3kg sugar'),
(150,15,'Toast bread'),
(150,18,'Earl Grey tea');

CREATE TABLE itemsintransactions (
    name VARCHAR(20),
    t_id int,
    amount int,
    FOREIGN KEY (name) REFERENCES items(name),
    FOREIGN KEY (t_id) REFERENCES transactions(t_id)
);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES
('6 cans of lemonade',1,1),
('Roast chicken',1,1),
('Pack of butter',1,1),
('Toast bread',1,1),
('2lof milk',1,2),
('Banana',1,3),
('3kg sugar',1,1),
('6 cans of lemonade',2,5),
('Pack of rice',2,1),
('6 cans of lemonade',3,3),
('Roast chicken',3,2),
('Pack of rice',3,1),
('Pack of butter',3,1),
('2lof milk',4,5),
('Banana',4,20),
('3kg sugar',4,8),
('6 cans of lemonade',5,10),
('Roast chicken',5,10),
('Pack of rice',5,10),
('Pack of butter',5,10),
('Toast bread',5,10),
('2lof milk',5,10),
('Banana',5,10),
('3kg sugar',5,10),
('Earl Grey tea',5,10);

CREATE VIEW ItemsLeft AS
SELECT items.name, (items.amount-sum(itemsintransactions.amount)) AS amount_left
FROM items, itemsintransactions
GROUP BY items.name
ORDER BY items.name;

score 0 · Answer 1 · answered Dec 02 '20 at 16:15

0

The items.amount in bold below must be inside an aggregate function, because the .amount field is not in the group by:

(items.amount-sum(itemsintransactions.amount))

answered Dec 02 '20 at 16:15

AaronLS

37,329
20
143
202

what aggreagte function would it be in? – Dec 02 '20 at 16:20

score 0 · Answer 2 · answered Dec 02 '20 at 16:25

Aggregate first in itemsintransactions and then do LEFT join of items to the results:

SELECT i.name, i.amount - COALESCE(t.amount, 0) AS amount_left
FROM items AS i 
LEFT JOIN (
  SELECT name, SUM(amount) AS amount
  FROM itemsintransactions 
  GROUP BY name
) AS t ON t.name = i.name
ORDER BY i.name

See the demo.
Results:

> name               | amount_left
> :----------------- | ----------:
> 2lof milk          |           5
> 3kg sugar          |          11
> 6 cans of lemonade |          11
> Banana             |          17
> Earl Grey tea      |           8
> Pack of butter     |           8
> Pack of rice       |          18
> Roast chicken      |           0
> Toast bread        |           4

O. Jones · Answer 3 · 2020-12-02T16:38:54.057

You have a (notorious and obsolete) comma-join in FROM items, itemsintransactions.

This is a problem because it makes MySQL generate and try to summarize all possible combinations of rows from your two tables, yielding rubbish from your SUM()s.

You want this instead.

FROM items
LEFT JOIN itemsintransactions USING (name)

Check it out here. https://www.db-fiddle.com/f/mpENTCRM2D7ZcT53V9tNza/0

SELECT items.name, items.amount, 
       SUM(itemsintransactions.amount) taken,
       items.amount - SUM(itemsintransactions.amount) remaining
FROM items
LEFT JOIN itemsintransactions USING (name)
GROUP BY items.name
ORDER BY items.name

Using LEFT JOIN in place of an ordinary inner JOIN allows rows from your items table to appear in your result set even if they have no corresponding itemstransactions entries.

You are, another answer to the contrary notwithstanding, allowed to mention items.amount in your SELECT clause because you group by items.name, and that is the primary key. There's no ambiguity about which items.amount value is the right one to use. In other words if you ran this query ....

SELECT items.name, ANY_VALUE(items.amount), 
       SUM(itemsintransactions.amount) taken,
       ANY_VALUE(items.amount) - SUM(itemsintransactions.amount) remaining
FROM items
LEFT JOIN itemsintransactions USING (name)
GROUP BY items.name
ORDER BY items.name

You'd get the desired result. ANY_VALUE() picks some value from the rows being aggregated, and in your case it always picks the right value.

Group By not working to calculate amount left in MySql

3 Answers3