InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 03:48:00 GMT20http://www.sqlservercentral.com/Forums/Topic819884-145-1.aspxGood Luck!!Tue, 17 Nov 2009 04:32:26 GMTsshalloehttp://www.sqlservercentral.com/Forums/Topic819884-145-1.aspx* goes out to buy a Lotto ticket * :-DTue, 17 Nov 2009 03:47:46 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic819884-145-1.aspxHi PaulThat's fantastic! .. worked a treat :)Many ThanksSeanTue, 17 Nov 2009 03:18:41 GMTsshalloehttp://www.sqlservercentral.com/Forums/Topic819884-145-1.aspxSean,I had to make some guesses, but try this:[code]SELECT supplier = ITM.poit_supplier, local_orders = SUM(CASE WHEN ORD.pord_pondcode = 'Local' THEN ITM.poit_valord ELSE 0 END), global_orders = SUM(CASE WHEN ORD.pord_pondcode <> 'Local' THEN ITM.poit_valord ELSE 0 END)FROM poitem ITMJOIN porder ORD ON ORD.pord_order = ITM.poit_order AND ORD.pord_supplier = ITM.poit_supplierJOIN plsupp SUP ON SUP.plsup_ndcode = ORD.pord_supndcodeWHERE ITM.poit_qtydel < ITM.poit_qtyordGROUP BY ITM.poit_supplier, SUP.plsup_minord;[/code]PaulTue, 17 Nov 2009 03:09:53 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic819884-145-1.aspxThanks for that I think I understand. However, the resulting data needs to populate a 3 column table:Col 1 - poit_supplier (Supplier Code - to be output only once for each supplier in the result)Col 2 - sum(poit_valord) (Sum of ALL Orders for a supplier placed locally)Col 3 - sum(poit_valord) (Sum of ALL Orders for a supplier placed non-locally)I hope this clarifies what I'm trying to achieve? Any ideas greatly appreciatedThanksSeanTue, 17 Nov 2009 02:20:53 GMTsshalloehttp://www.sqlservercentral.com/Forums/Topic819884-145-1.aspxAs the queries are the same barring the test for pord_pondcode the below would eb more efficient than two queries.Also you were grouping by plsup_minord but not selecting it out.[code="sql"]select case when pord_pondcode = 'Local' then 'Local' else 'Non-Local' end,sum(poit_valord),poit_supplierfrom poitem,porder,plsuppwhere poit_order = pord_orderAND poit_qtydel < poit_qtyordAND pord_supplier = poit_supplierAND plsup_ndcode = pord_supndcodeGROUP BY poit_supplier,plsup_minord,case when pord_pondcode = 'Local' then 'Local' else 'Non-Local' end[/code]Tue, 17 Nov 2009 02:07:37 GMTDave Ballantynehttp://www.sqlservercentral.com/Forums/Topic819884-145-1.aspxHiI'm fairly new to MS SQL and require some assistance if possible.I have two similar query statements one looking for sum of local orders and the other looking for sum of global orders. However, I need to combine the two queries into one the problem is each of the constructed queries require the Group By clause in order to out put the required detail. (see below)-- Query One select sum(poit_valord),poit_supplierfrom poitem,porder,plsuppwhere poit_order = pord_orderAND poit_qtydel < poit_qtyordAND pord_pondcode = 'Local'AND pord_supplier = poit_supplierAND plsup_ndcode = pord_supndcodeGROUP BY poit_supplier,plsup_minord -- Query Twoselect sum(poit_valord),poit_supplierfrom poitem,porder,plsuppwhere poit_order = pord_orderAND poit_qtydel < poit_qtyordAND pord_pondcode <> 'Local'AND pord_supplier = poit_supplierAND plsup_ndcode = pord_supndcodeGROUP BY poit_supplier,plsup_minordAny help would be very much appreciated.ThanksSeanTue, 17 Nov 2009 01:58:10 GMTsshalloe