如何对其他列的条件求和 [英] 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

和mysql显示数据是这样的

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

我尝试的是根据 po 编号对数据求和材料描述并没有像这样在采购订单号上汇总所有数量:

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

这是我用过的桌子

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

推荐答案

使用下面的查询 :-

 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.

这篇关于如何对其他列的条件求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！