Post reply

Multi Query Group BY

  • November 17, 2009 at 1:58 am

    #228023

    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

  • November 17, 2009 at 2:07 am

    #1080099

    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[/url]

  • November 17, 2009 at 2:20 am

    #1080105

    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

  • November 17, 2009 at 3:09 am

    #1080117

    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
    SQLkiwi blog
    @SQL_Kiwi

  • November 17, 2009 at 3:18 am

    #1080119

    Hi Paul

    That's fantastic! .. worked a treat 🙂

    Many Thanks

    Sean

  • November 17, 2009 at 3:47 am

    #1080128

    * goes out to buy a Lotto ticket * 😀

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • November 17, 2009 at 4:32 am

    #1080150

    Good Luck!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply