SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multi Query Group BY


Multi Query Group BY

Author
Message
sshalloe
sshalloe
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 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
Dave Ballantyne
Dave Ballantyne
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7446 Visits: 8370
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
sshalloe
sshalloe
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 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
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36934 Visits: 11361
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
sshalloe
sshalloe
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 24
Hi Paul

That's fantastic! .. worked a treat Smile

Many Thanks

Sean
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36934 Visits: 11361
* goes out to buy a Lotto ticket * :-D



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
sshalloe
sshalloe
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 24
Good Luck!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search