asked
How do I conditionally sum rows based on another column
I have a data set that I need to conditionally sum rows based on another column The logic is as follow If Line, Lot, & Comments are the same then sum all rows ELSE row Line 8 & 9 meet this criteria so I want to see 1 row for 8 (sum 10) and one row for 9 (sum 15) wh_num abs_num comments line lot order_ qty KU22 007227310311 90289804 9 6FA4202088 280642 2.00 KU22 007227310311 90289804 9 6FA4202088 280642 13.00 KU22 007227310715 P000774394 3 0000999900 280642 15.00 KU22 007227311117 20749080 8 12 C1 246 14 280642 2.00 KU22 007227311117 20749080 8 12 C1 246 14 280642 8.00 KU22 007227311120 21467393 10 6 C1 227 13 280642 30.00 KU22 007227312123 90264062 4 30203274 280642 10.00 KU22 007227312555 P000801184 2 BSDL C253 280642 20.00 KU22 007227313305 20753230 5 5 C2 234 14 280642 20.00 KU22 007227348348 P000815930 1 ASWH D232 280642 20.00 Here is my query SELECT auditlog.wh_num , auditlog.abs_num , auditlog.comments , auditlog.po_line AS line , auditlog.lot , auditlog.po_number AS order_ , ordhdr.pro_number , auditlog.item_qty AS qty FROM auditlog INNER JOIN ordhdr ON auditlog.co_num = ordhdr.co_num AND auditlog.wh_num = ordhdr.wh_num AND auditlog.po_number = ordhdr.order_ WHERE ( auditlog.trans_type = 'ig' ) GROUP BY auditlog.wh_num , auditlog.abs_num , auditlog.comments , auditlog.po_line , auditlog.lot , auditlog.po_number , ordhdr.pro_number , auditlog.item_qty HAVING ( SUM(auditlog.item_qty) <> 0 ) AND ( SUM(auditlog.sugg_qty) <> 0 ) AND ( auditlog.wh_num = 'KU22' ) AND ( auditlog.po_number = '280642' )
in the data given, when line, lot and comments are the same, then so are wh_num, abs_num and order_ : is that true of all your data?
Not sure what your ordhdr table looks like but you'd need to include the aggregate clause (i.e. SUM) in the SELECT and only do the GROUP BY on the other columns as follows: SELECT auditlog.wh_num, auditlog.abs_num, auditlog.comments, auditlog.po_line AS line, auditlog.lot, auditlog.po_number AS order_, SUM(auditlog.item_qty) AS SumOfQty, SUM(auditlog.sugg_qty) AS SumOfSugg_Qty FROM dbo.audit_log INNER JOIN ordhdr ON auditlog.co_num = ordhdr.co_num AND auditlog.wh_num = ordhdr.wh_num AND auditlog.po_number = ordhdr.order_ WHERE ( auditlog.trans_type = 'ig' ) GROUP BY auditlog.wh_num , auditlog.abs_num , auditlog.comments , auditlog.po_line , auditlog.lot , auditlog.po_number , ordhdr.pro_number , HAVING ( SUM(auditlog.item_qty) AND ( SUM(auditlog.sugg_qty) <> 0 ) AND ( auditlog.wh_num = 'KU22' ) AND ( auditlog.po_number = '280642' ) Hope this helps.
15 People are following this question.
