mysql

MySQL Get the sum of Same Product from Same PO and order them as per Product Name

I am having a table as below, where I am receiving products for each PO, multiple times and need to Get Total Quantity received as per their Respective PO and order them as per Product Name

enter image description here

What I want is

enter image description here

I tried below SQL query:

SELECT Product_Name, PONo , sum(Quantity) AS Total_Quantity 
FROM GrnTable 
GROUP BY Product_Name;

but it is summing up all quantities, from all PO Number.

Solution

  • I would suggest grouping by the PO number first, and then also group by the Product Name : Group By PONo., Product Name

    By adding the group by to the PONo, you accomplish the first task of grouping by respective PO.

    SELECT Product as Product_Name, PONo , sum(Quantity) AS Total_Quantity 
FROM GrnTable 
GROUP BY PONo,Product;

    EX:

    TestTable

    PONo Product Quantity
    PO-18 Booster 10
    PO-18 Booster 10
    PO-18 Booster 20
    PO-18 Booster 20
    PO-19 Booster 10
    PO-19 Booster 15
    PO-20 Booster 20
    PO-20 Booster 25
    PO-18 Booster 5
    PO-18 Booster 15
    PO-18 Bottle Cap 25
    PO-18 Bottle Cap 5

    TestResults

    Product_Name PONo Total_Quantity
    Booster PO-18 80
    Booster PO-19 25
    Booster PO-20 45
    Bottle Cap PO-18 30