Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multi Query Group BY Expand / Collapse
Author
Message
Posted Tuesday, November 17, 2009 1:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 20, 2010 2:59 AM
Points: 8, Visits: 24
Hi

I'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_supplier
from poitem,porder,plsupp
where poit_order = pord_order
AND poit_qtydel < poit_qtyord
AND pord_pondcode = 'Local'
AND pord_supplier = poit_supplier
AND plsup_ndcode = pord_supndcode
GROUP BY poit_supplier,plsup_minord

-- Query Two

select sum(poit_valord),poit_supplier
from poitem,porder,plsupp
where poit_order = pord_order
AND poit_qtydel < poit_qtyord
AND pord_pondcode <> 'Local'
AND pord_supplier = poit_supplier
AND plsup_ndcode = pord_supndcode
GROUP BY poit_supplier,plsup_minord

Any help would be very much appreciated.

Thanks

Sean
Post #819884
Posted Tuesday, November 17, 2009 2:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:12 AM
Points: 1,949, Visits: 8,309
As 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.



select case when pord_pondcode = 'Local' then 'Local' else 'Non-Local' end,sum(poit_valord),poit_supplier
from poitem,porder,plsupp
where poit_order = pord_order
AND poit_qtydel < poit_qtyord
AND pord_supplier = poit_supplier
AND plsup_ndcode = pord_supndcode
GROUP BY poit_supplier,plsup_minord,case when pord_pondcode = 'Local' then 'Local' else 'Non-Local' end





Clear Sky SQL
My Blog
Kent user group
Post #819889
Posted Tuesday, November 17, 2009 2:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 20, 2010 2:59 AM
Points: 8, Visits: 24
Thanks 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 appreciated

Thanks

Sean
Post #819897
Posted Tuesday, November 17, 2009 3:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:59 AM
Points: 11,194, Visits: 11,135
Sean,

I had to make some guesses, but try this:

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 ITM
JOIN porder ORD
ON ORD.pord_order = ITM.poit_order
AND ORD.pord_supplier = ITM.poit_supplier
JOIN plsupp SUP
ON SUP.plsup_ndcode = ORD.pord_supndcode
WHERE ITM.poit_qtydel < ITM.poit_qtyord
GROUP BY
ITM.poit_supplier,
SUP.plsup_minord;

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #819909
Posted Tuesday, November 17, 2009 3:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 20, 2010 2:59 AM
Points: 8, Visits: 24
Hi Paul

That's fantastic! .. worked a treat :)

Many Thanks

Sean
Post #819911
Posted Tuesday, November 17, 2009 3:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:59 AM
Points: 11,194, Visits: 11,135
* goes out to buy a Lotto ticket *



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #819924
Posted Tuesday, November 17, 2009 4:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 20, 2010 2:59 AM
Points: 8, Visits: 24
Good Luck!!
Post #819949
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse