Description:
Dear sir, I've faced a SUM() problem. I've searched so many article about the problem if I use wrong statement but there was no article what exactly happend to me. I'm still not sure if this is a bug or my wrong statement.
- I want to get a 'Ready Stock Report'. So I've two tables - One is 'Purchase' & another is 'Sale' in 'Stock' Database. My plan was - if I calculate the Purchase & Sale table (PurchaseQuantity - SaleQuantity = Stock) I'd get the Stock report. So I wrote the statement & got result like below -
How to repeat:
###
### Starts from here
###
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 6.0.4-alpha-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
####
#### My 'Stock' Database has created
####
mysql> CREATE DATABASE Stock;
Query OK, 1 row affected (0.00 sec)
####
#### Selects 'Stock' Database here
####
mysql> USE Stock;
Database changed
####
#### Creates 'Purchase' table. Where I'd mention product data & Purchase Qty.
####
mysql> CREATE TABLE Purchase(
-> ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> Product CHAR(20),
-> Quantity TINYINT UNSIGNED
-> );
Query OK, 0 rows affected (0.03 sec)
####
#### Creates 'Sale' table. Where I'd mention sold product data & Qty.
####
mysql> CREATE TABLE Sale(
-> ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> PurchaseID INT UNSIGNED NOT NULL,
-> Quantity TINYINT UNSIGNED
-> );
Query OK, 0 rows affected (0.05 sec)
####
#### Here I enter two data for example - 'TV 5pcs' & 'PC 10pcs' i just purchase
####
mysql> INSERT INTO Purchase(Product, Quantity)
-> VALUES('TV', 5);
Query OK, 1 row affected (0.03 sec)
mysql> INSERT INTO Purchase(Product, Quantity)
-> VALUES('PC', 10);
Query OK, 1 row affected (0.03 sec)
####
#### Suppose, I just Sale '1pc TV' here by PurshaseID
#### (So my TV stock goes to 4 out of 5)
####
mysql> INSERT INTO Sale(PurchaseID, Quantity)
-> VALUES(1, 1);
Query OK, 1 row affected (0.02 sec)
####
#### And Suppose, I just Sale now '1pc PC' by PurshaseID
#### (So my PC stock goes to 9 out of 10)
####
mysql> INSERT INTO Sale(PurchaseID, Quantity)
-> VALUES(2, 1);
Query OK, 1 row affected (0.01 sec)
####
#### Now please think, I just Sale another time '2pc TV' by PurshaseID
#### (Remember my TV stock was 4 out of 5, and now 2 more - means, 2 out of 5
#### is my Ready Stock) Now lets see the total result...
####
mysql> INSERT INTO Sale(PurchaseID, Quantity)
-> VALUES(1, 2);
Query OK, 1 row affected (0.02 sec)
####
#### Here is my total Purchase qty. by Product - which is perfect
####
mysql> SELECT p.Product, SUM(p.Quantity) AS TotalPurchase
-> FROM Purchase p
-> GROUP BY Product;
+---------+---------------+
| Product | TotalPurchase |
+---------+---------------+
| PC | 10 |
| TV | 5 |
+---------+---------------+
2 rows in set (0.00 sec)
####
#### And, here is my total Sold qty. by Product - which is absolutely right.
#### NOW LETS SEE WHERE I'M GETTING UNEXPECTED RESULT
####
mysql> SELECT p.Product, SUM(s.Quantity) AS TotalSale
-> FROM Sale s, Purchase p
-> WHERE s.PurchaseID = p.ID
-> GROUP BY p.ID;
+---------+-----------+
| Product | TotalSale |
+---------+-----------+
| TV | 3 |
| PC | 1 |
+---------+-----------+
2 rows in set (0.00 sec)
####
#### HERE IS THE RESULT WHEN I'M getting like this - Here 'TotalSale' Result is
#### quite perfect. but just take a look at the 'TotalPurchase' of Product 'TV'
#### IT'S '10' BUT IT'D BE ONLY 5. I dont understand where the another 5 is #### coming from.
####
mysql> SELECT p.Product, SUM(p.Quantity) AS TotalPurchase, SUM(s.Quantity) AS TotalSale
-> FROM Purchase p LEFT JOIN Sale s ON s.PurchaseID = p.ID
-> GROUP BY p.Product;
+---------+---------------+-----------+
| Product | TotalPurchase | TotalSale |
+---------+---------------+-----------+
| PC | 10 | 1 |
| TV | 10 | 3 |
+---------+---------------+-----------+
2 rows in set (0.00 sec)
mysql>
Suggested fix:
I'm not sure that i wrote the statement right or not. please suggest me what to do to get result accurate. Or its just a bug!
Description: Dear sir, I've faced a SUM() problem. I've searched so many article about the problem if I use wrong statement but there was no article what exactly happend to me. I'm still not sure if this is a bug or my wrong statement. - I want to get a 'Ready Stock Report'. So I've two tables - One is 'Purchase' & another is 'Sale' in 'Stock' Database. My plan was - if I calculate the Purchase & Sale table (PurchaseQuantity - SaleQuantity = Stock) I'd get the Stock report. So I wrote the statement & got result like below - How to repeat: ### ### Starts from here ### Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 43 Server version: 6.0.4-alpha-community MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. #### #### My 'Stock' Database has created #### mysql> CREATE DATABASE Stock; Query OK, 1 row affected (0.00 sec) #### #### Selects 'Stock' Database here #### mysql> USE Stock; Database changed #### #### Creates 'Purchase' table. Where I'd mention product data & Purchase Qty. #### mysql> CREATE TABLE Purchase( -> ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> Product CHAR(20), -> Quantity TINYINT UNSIGNED -> ); Query OK, 0 rows affected (0.03 sec) #### #### Creates 'Sale' table. Where I'd mention sold product data & Qty. #### mysql> CREATE TABLE Sale( -> ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> PurchaseID INT UNSIGNED NOT NULL, -> Quantity TINYINT UNSIGNED -> ); Query OK, 0 rows affected (0.05 sec) #### #### Here I enter two data for example - 'TV 5pcs' & 'PC 10pcs' i just purchase #### mysql> INSERT INTO Purchase(Product, Quantity) -> VALUES('TV', 5); Query OK, 1 row affected (0.03 sec) mysql> INSERT INTO Purchase(Product, Quantity) -> VALUES('PC', 10); Query OK, 1 row affected (0.03 sec) #### #### Suppose, I just Sale '1pc TV' here by PurshaseID #### (So my TV stock goes to 4 out of 5) #### mysql> INSERT INTO Sale(PurchaseID, Quantity) -> VALUES(1, 1); Query OK, 1 row affected (0.02 sec) #### #### And Suppose, I just Sale now '1pc PC' by PurshaseID #### (So my PC stock goes to 9 out of 10) #### mysql> INSERT INTO Sale(PurchaseID, Quantity) -> VALUES(2, 1); Query OK, 1 row affected (0.01 sec) #### #### Now please think, I just Sale another time '2pc TV' by PurshaseID #### (Remember my TV stock was 4 out of 5, and now 2 more - means, 2 out of 5 #### is my Ready Stock) Now lets see the total result... #### mysql> INSERT INTO Sale(PurchaseID, Quantity) -> VALUES(1, 2); Query OK, 1 row affected (0.02 sec) #### #### Here is my total Purchase qty. by Product - which is perfect #### mysql> SELECT p.Product, SUM(p.Quantity) AS TotalPurchase -> FROM Purchase p -> GROUP BY Product; +---------+---------------+ | Product | TotalPurchase | +---------+---------------+ | PC | 10 | | TV | 5 | +---------+---------------+ 2 rows in set (0.00 sec) #### #### And, here is my total Sold qty. by Product - which is absolutely right. #### NOW LETS SEE WHERE I'M GETTING UNEXPECTED RESULT #### mysql> SELECT p.Product, SUM(s.Quantity) AS TotalSale -> FROM Sale s, Purchase p -> WHERE s.PurchaseID = p.ID -> GROUP BY p.ID; +---------+-----------+ | Product | TotalSale | +---------+-----------+ | TV | 3 | | PC | 1 | +---------+-----------+ 2 rows in set (0.00 sec) #### #### HERE IS THE RESULT WHEN I'M getting like this - Here 'TotalSale' Result is #### quite perfect. but just take a look at the 'TotalPurchase' of Product 'TV' #### IT'S '10' BUT IT'D BE ONLY 5. I dont understand where the another 5 is #### coming from. #### mysql> SELECT p.Product, SUM(p.Quantity) AS TotalPurchase, SUM(s.Quantity) AS TotalSale -> FROM Purchase p LEFT JOIN Sale s ON s.PurchaseID = p.ID -> GROUP BY p.Product; +---------+---------------+-----------+ | Product | TotalPurchase | TotalSale | +---------+---------------+-----------+ | PC | 10 | 1 | | TV | 10 | 3 | +---------+---------------+-----------+ 2 rows in set (0.00 sec) mysql> Suggested fix: I'm not sure that i wrote the statement right or not. please suggest me what to do to get result accurate. Or its just a bug!