Multi Query Group BY

  • 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

  • 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]

  • 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

  • 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

  • Hi Paul

    That's fantastic! .. worked a treat 🙂

    Many Thanks

    Sean

  • * goes out to buy a Lotto ticket * 😀

  • Good Luck!!

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

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