I'm having a really odd problem.. this is basic SQL, so it should be easy for most of you. For some strange reason, the quantity is not being summed up. Any idea why this would be happening? Everything else is grouped. The SQL is below, and some of the results are below that (you can see that it SHOULD be grouping them properly).
SELECT TOP 100 PERCENT dbo.cp_elements.campaign_id, dbo.cp_campaigns.name AS campaign_name, dbo.cp_elements.item_no, dbo.cp_elements.name,
dbo.cp_orderables.est_qty, dbo.cp_orderables.qty_increment, SUM(dbo.cp_order_detail.qty) as qty, dbo.cp_attribs.price
FROM dbo.cp_orderables INNER JOIN
dbo.cp_elements ON dbo.cp_orderables.element_id = dbo.cp_elements.element_id INNER JOIN
dbo.cp_attribs ON dbo.cp_orderables.orderable_id = dbo.cp_attribs.orderable_id INNER JOIN
dbo.cp_order_detail ON dbo.cp_attribs.attrib_id = dbo.cp_order_detail.attrib_id INNER JOIN
dbo.cp_selected_shiptos ON dbo.cp_order_detail.shipto_id = dbo.cp_selected_shiptos.shipto_id INNER JOIN
dbo.cp_campaigns ON dbo.cp_elements.campaign_id = dbo.cp_campaigns.campaign_id
GROUP BY dbo.cp_elements.campaign_id, dbo.cp_campaigns.name, dbo.cp_elements.item_no, dbo.cp_elements.name, dbo.cp_selected_shiptos.state,
dbo.cp_orderables.qty_increment, dbo.cp_attribs.price, dbo.cp_orderables.est_qty
ORDER BY dbo.cp_elements.item_no
I noticed that u have missing the dbo.cp_selected_shiptos.state column in SELECT COLUMNS. You have to give all the group by columns in SELECTed columns. Also Group by clause should have the ORDER BY COLUMN. This might be the reason that u face.