SQLTeam.com | Weblogs | Forums

Join Help

Hey Guys,

I am just learning SQL getting through the basics, but I have come to a halt trying to join these two tables together when i try to add in a sum column with a Case imbedded.

I have no idea where i am going wrong!! any and all help appreciated!

code i have so far below:

SELECT h.order_number,
i.supply_material,
Sum(CASE
WHEN h.operation_qty = h.confirmed_qty THEN
h.confirmed_1 + h.confirmed_2 + h.confirmed_3
ELSE h.standard_1 + h.standard_2 + h.standard_3 + h.standard_4
END) AS "Predicted Hours"

    FROM og_surf_snop_dm.prod_order_operation_t 

GROUP BY h.order_number

INNER JOIN og_surf_snop_dm.sop_mps_pegging_data_mv i
ON i.demand_order = h.order_number;

You need to GROUP BY both columns:

GROUP BY h.order_number, i.supply_material

or, less likely, remove i.supply_material from the query.

1 Like

Try this

SELECT h.order_number,
i.supply_material,
Sum(CASE WHEN h.operation_qty = h.confirmed_qty
THEN h.confirmed_1 + h.confirmed_2 + h.confirmed_3
ELSE h.standard_1 + h.standard_2 + h.standard_3 + h.standard_4
END)
AS "Predicted Hours"
FROM og_surf_snop_dm.prod_order_operation_t

INNER JOIN og_surf_snop_dm.sop_mps_pegging_data_mv i
ON i.demand_order = h.order_number;

GROUP BY h.order_number, i.supply_material