Reputation: 67
Correct me if the question title is wrong ,
i have table in MySQL Named sales
-------------------------------------------------------------------------------------
| PRODUCT_NAME | PRODUCT_QUANTITY | PRODUCT_TYPE | In_Box_Count_Sell | ExpiryDate |
-------------------------------------------------------------------------------------
| kkkkk | 4 | Count | 1 | 2021-02-28 |
| kkkkk | 4 | Count | 1 | 2021-02-21 |
| yuyuyu | 3 | Count | 5 | 2021-02-21 |
-------------------------------------------------------------------------------------
Another Table Named depot
--------------------------------
| ITEM_NAME | PRODUCT_QUANTITY |
--------------------------------
| kkkkk | 12 |
| yuyuyu | 15 |
--------------------------------
I have used this query :
SELECT `ExpiryDate`,`PRODUCT_NAME` As pdname,sales.`PRODUCT_QUANTITY`,`In_Box_Count_Sell`,`PRODUCT_TYPE`,(`depot`.`PRODUCT_QUANTITY`) As InDepotQuent FROM ((`sales`
INNER JOIN depot ON sales.PRODUCT_NAME = depot.ITEM_NAME)
INNER JOIN bill_info ON sales.BILL_NUMBER = bill_info.BILL_NUMBER)
WHERE bill_info.BILL_TYPE = 'مشتريات'
ORDER BY `sales`.`PRODUCT_NAME` ASC
And its result is :
---------------------------------------------------------------------------------------------
| ExpiryDate | pdname | PRODUCT_QUANTITY | In_Box_Count_Sell | PRODUCT_TYPE | InDepotQuent |
---------------------------------------------------------------------------------------------
| 2021-02-21 | kkkkk | 10 | 1 | Count | 12 |
| 2021-02-28 | kkkkk | 4 | 1 | Count | 12 |
| 2021-08-25 | yuyuy | 3 | 5 | Count | 15 |
---------------------------------------------------------------------------------------------
what i need to get is the atc
= the sum of PRODUCT_QUANTITY
- the sum of InDepotQuent
and the result will be like this :
---------------------------------------------------------------------------------------------------
| ExpiryDate | pdname | PRODUCT_QUANTITY | In_Box_Count_Sell | PRODUCT_TYPE | InDepotQuent | atc |
---------------------------------------------------------------------------------------------------
| 2021-02-21 | kkkkk | 10 | 1 | Count | 12 | 2 |
| 2021-02-28 | kkkkk | 4 | 1 | Count | 12 | 2 |
| 2021-08-25 | yuyuy | 3 | 5 | Count | 15 | 12 |
---------------------------------------------------------------------------------------------------
So the sum of PRODUCT_QUANTITY = (14) - sum of InDepotQuent = (12)
The atc = 2
And taking into account the name of the pdname are similar in a table !
Upvotes: 1
Views: 62
Reputation: 1271221
You can use window functions, if I understand correctly:
SELECT ExpierDate, PRODUCT_NAME As pdname, s.PRODUCT_QUANTITY,
In_Box_Count_Sell, PRODUCT_TIPE, d.PRODUCT_QUANTITY As InDepotQuent,
SUM(s.PRODUCT_QUANTITY) OVER (PARTITION BY PRODUCT_NAME) - d.PRODUCT_QUANTITY as atc
FROM sales s JOIN
depot d
ON s.PRODUCT_NAME = d.ITEM_NAME JOIN
bill_info bi
ON s.BILL_NUMBER = bi.BILL_NUMBER
WHERE bi.BILL_TIPE = 'مشتريات'
ORDER BY s.PRODUCT_NAME ASC;
Upvotes: 1