Gusti Aldi Gusti Aldi - 6 months ago 14
SQL Question

How to sum with conditions on other columns

me trying to get sum on deliveryqty with group by po number on other tables like this

SELECT
po.PONumber,
po.PODate,
po.customername,
po.Description,
SUM(spb.DeliveryQty)
FROM
tb_po AS po
LEFT OUTER JOIN
tb_spb AS spb ON po.PONumber = spb.PONumber
GROUP BY
po.PONumber,
po.Description


and mysql show data like this

PONUMBER podate customername description deliveryqty
4600293473 04/04/2016 Balikpapan-CLUB AMDK CLUB 1500 ML 810
4600293473 04/04/2016 Balikpapan-CLUB AMDK CLUB 600 ML 810
4600293473 04/04/2016 Balikpapan-CLUB AMDK CLUB GALON 19 LT 810
4600293487 04/04/2016 Balikpapan-CLUB AMDK CLUB GALON 19 LT 800
4600293489 04/04/2016 Balikpapan-CLUB AMDK CLUB 240 ML 1200
4600293489 04/04/2016 Balikpapan-CLUB AMDK CLUB 330 ML 1200


what i try is data is summed based on po number & material description not summed all qty just on po number like this :

PONUMBER podate customername description deliveryqty
4600293473 04/04/2016 Balikpapan-CLUB AMDK CLUB 1500 ML 250
4600293473 04/04/2016 Balikpapan-CLUB AMDK CLUB 600 ML 440
4600293473 04/04/2016 Balikpapan-CLUB AMDK CLUB GALON 19 LT 120
4600293487 04/04/2016 Balikpapan-CLUB AMDK CLUB GALON 19 LT 800
4600293489 04/04/2016 Balikpapan-CLUB AMDK CLUB 240 ML 1200
4600293489 04/04/2016 Balikpapan-CLUB AMDK CLUB 330 ML 0


here's the table i used

tb_po

PONUMBER podate customername description
4600293473 04/04/2016 Balikpapan-CLUB AMDK CLUB 1500 ML
4600293473 04/04/2016 Balikpapan-CLUB AMDK CLUB 600 ML
4600293473 04/04/2016 Balikpapan-CLUB AMDK CLUB GALON 19 LT
4600293487 04/04/2016 Balikpapan-CLUB AMDK CLUB GALON 19 LT
4600293489 04/04/2016 Balikpapan-CLUB AMDK CLUB 240 ML
4600293489 04/04/2016 Balikpapan-CLUB AMDK CLUB 330 ML


tb_spb

PONUMBER podate customername description deliveryqty
4600293473 04/04/2016 Balikpapan-CLUB AMDK CLUB 1500 ML 125
4600293473 04/04/2016 Balikpapan-CLUB AMDK CLUB 1500 ML 125
4600293473 04/04/2016 Balikpapan-CLUB AMDK CLUB 600 ML 440
4600293473 04/04/2016 Balikpapan-CLUB AMDK CLUB GALON 19 LT 120
4600293487 04/04/2016 Balikpapan-CLUB AMDK CLUB GALON 19 LT 400
4600293487 04/04/2016 Balikpapan-CLUB AMDK CLUB GALON 19 LT 400
4600293489 04/04/2016 Balikpapan-CLUB AMDK CLUB 240 ML 1200
4600293489 04/04/2016 Balikpapan-CLUB AMDK CLUB 240 ML 1200
4600293489 04/04/2016 Balikpapan-CLUB AMDK CLUB 240 ML 1200
4600293489 04/04/2016 Balikpapan-CLUB AMDK CLUB 330 ML 0

Answer

Use this below query :-

 CREATE TEMPORARY TABLE t1 (PONumber decimal(18, 2) NOT NULL,POdate date NOT NULL,customername varchar(200) NOT NULL,description varchar(200) NOT NULL);
 INSERT INTO t1(PONumber,POdate,customername,description) 
 select distinct PONumber,POdate,customername,description from tb_po;

 select  p.PONumber,p.podate,p.customername,p.description,
(select sum(q.deliveryqty) from tb_spb q where p.PONumber=q.PONumber AND p.description = q.description) as Total
 from t1 p;

drop table t1;

It will give you the required output.