mysql - 具有多个 where 子句和组的多重选择

Question

我正在尝试从 mysql 数据库中获取数据以生成报告。但是当我有多个带有多个 where 和 group 子句的选择时，我很挣扎。

我想做的是我希望第二个和第三个选择语句中的 SUM(pf.meter) 应该根据相关选择语句中给出的 where 子句返回值并且需要分组按主选择语句按子句分组。

请找到附件。那里没有计划的数量对于所有行都是相同的它不能是那样的。它应该按客户、颜色、样式分组。

这是我的SQL

SELECT  pm.date, 
        pm.customer, 
        pf.style, 
        pf.color, 
        COUNT(pf.roll_no) AS Roll_QTY,
        SUM(pf.yard) AS Yard_QTY, 
        SUM(pf.meter) AS Meter_QTY, 
        SUM(pf.kilo) AS Kilo_QTY, 
        (
            SELECT SUM(pf.meter)
            FROM packinglists_fabrics_items pf, packinglists_main pm
            WHERE pf.aql_status = 0
            AND pf.grn_no= pm.gp_no[
            AND pm.date BETWEEN {DateR, RANGE1} AND {DateR, RANGE2}]
        ) FB_Not_Passed_Qty,    
        (
            SELECT SUM(pf.meter)
            FROM packinglists_fabrics_items pf, packinglists_main pm
            WHERE pf.aql_status = 1
            AND pf.tag_gen = 0
            AND pf.grn_no= pm.gp_no[
            AND pm.date BETWEEN {DateR, RANGE1} AND {DateR, RANGE2}]
        ) Not_Planned_Qty
FROM packinglists_main pm, packinglists_fabrics_items pf
WHERE pf.grn_no= pm.gp_no[
AND pm.date BETWEEN {DateR, RANGE1} AND {DateR, RANGE2}]
GROUP BY pm.customer, pf.style, pf.color

Answer 1

SELECT pm.date, pm.customer, pf.style, pf.color, COUNT(pf.roll_no) AS Roll_QTY,
       SUM(pf.yard) AS Yard_QTY, SUM(pf.meter) AS Meter_QTY, SUM(pf.kilo) AS Kilo_QTY, 
    SUM( case when pf.aql_status = 0  then pf.meter else 0 end ) FB_Not_Passed_Qty,
    SUM( case when pf.aql_status = 1 and  pf.tag_gen = 0  then pf.meter else 0 end ) Not_Planned_Qty
FROM packinglists_main pm,
     packinglists_fabrics_items pf
WHERE pf.grn_no= pm.gp_no[
  AND pm.date BETWEEN {DateR,
                       RANGE1} AND {DateR,
                                    RANGE2}]
GROUP BY pm.customer, pf.style, pf.color