CODEWITHSUNDEEP
sqlsubqueryinformix
walter_iv
walter_iv

Reputation: 21

Group By in a Subquery

Trying to return the sum of all orders under each part number. The main issue is with the select subquery.

SELECT i.customer_no AS CustNumber,
       i.part_no AS PartNumber, 
       SUM(f.weight) AS ForecastWeight,
       (SELECT SUM(o.ord_ord_wgt)
          FROM ortord_rec o 
         WHERE (o.ord_comp_dt < 20191126 AND o.ord_comp_dt > 20190925)
         GROUP BY o.ord_cus_part_no
       ) AS UsageWeight,
       p.cpt_prog_mat
  FROM fit_part_info i  
 INNER JOIN fit_part_forecast f ON f.part_info_id = i.id
 INNER JOIN orrcpt_rec p ON i.part_no = p.cpt_cus_part_no
 INNER JOIN ortord_rec o ON i.part_no = o.ord_cus_part_no
 WHERE accounting_period IN ('201911', '201912', '202001', '202002') AND cpt_prog_mat = "Y"
 GROUP BY i.part_no, o.ord_cus_part_no, i.customer_no, p.cpt_prog_mat
 ORDER BY i.part_no;

When I run this, I get the error:

A subquery has returned not exactly one row.

Upvotes: 1

Views: 59

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271091

Presumably, you intend a correlated subquery:

(SELECT SUM(o.ord_ord_wgt)
 FROM ortord_rec o 
 WHERE o.ord_comp_dt < 20191126 AND
       o.ord_comp_dt > 20190925 AND
       o.ord_cus_part_no = i.part_no
) AS UsageWeight,

Upvotes: 1

Related Questions