colombo colombo - 6 months ago 15
SQL Question

Multiple Select with multiple where clauses and Groups

I am trying to get the data from the mysql database for generating report.but I am struggling when I have multiple selects with multiple where and group clauses.

what I want to do is I want

SUM(pf.meter)
in the second and third select statements should return the values according to the where clause is given in relevant select statement and needs to group by main select statements Group by clause.

enter image description here
please find the attachemnet.in there not planned qty is same for all the rows it cannot be like that.it should be group by customer, color, style.

Here is my 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
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