how do i use group by in only one row?

debugcn Published at Dev

Adeeb Mark

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 !

Gordon Linoff

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;

Collected from the Internet

Please contact [email protected] to delete if infringement.