Jump to content

Nic.

Members
 View Profile  See their activity

  • Posts

    6

  • Joined

  • Last visited

 Content Type 

Posts posted by Nic.

    Results differ when using CASE & IF Vs. putting the condition in the where clause

    in SQL

    Posted

    Am I misusing the CASE & IF functions in some way? I expected each query to return the exact same value 1327.73.
    select sum(ordertotal) as Dec_Sales
    from orderexport
    where orderstatus = 'declined'
    • RESULT = 1327.73

    select CASE
    when orderstatus = 'declined'
    then sum(ordertotal)
    else NULL
    end as Dec_Sales
    FROM orderexport
    • RESULT = {null}

    select if(orderstatus = 'declined',sum(ordertotal),0) as Dec_Sales
    FROM orderexport
    • RESULT = 0

    Will an inline Query work?

    in SQL

    Posted

    Oh man, I'm on a roll:
    SELECT
    afid
    , sid
    , sum(order_Status != "DECLINED") as Approved
    , sum(order_Status = "DECLINED") as Declined
    , monthname(date_Of_Sale)
    FROM
    little_test_table
    Group BY
    month(date_of_sale), afid, sid
    ;
    Hopefully this stuff will come in handy for other people! I've spent countless hours on this site trying to educate myself on creating queries. It must finally be sinking in ...
    Thanks for letting me post. ;)
    Nichole

    Will an inline Query work?

    in SQL

    Posted

    Maybe just writing out the problem was enough. Finally got something to work:

     

    SELECT
    approved_trans.total as approved_total,
    declined_trans.total as declined_total
    FROM
    (
    SELECT
    COUNT(little_test_table.order_id) AS total
    FROM
    little_test_table
    WHERE
    MONTHNAME(little_test_table.date_of_Sale) = MONTHNAME(little_test_table.date_of_sale)
    AND little_test_table.AFID = little_test_table.AFID
    AND little_test_table.sid = little_test_table.SID
    AND little_test_table.order_status != 'declined'
    ) approved_trans,
    (
    SELECT
    COUNT(little_test_table.order_id) AS total
    FROM
    little_test_table
    WHERE
    MONTHNAME(little_test_table.date_of_Sale) = MONTHNAME(little_test_table.date_of_sale)
    AND little_test_table.AFID = little_test_table.AFID
    AND little_test_table.sid = little_test_table.SID
    AND little_test_table.order_status = 'declined'
    ) declined_trans

    Will an inline Query work?

    in SQL

    Posted

    In a perfect world, I would have one query which would give me summarized info from detail rows. I have tried a million different inline queries and just cannot get it to work correctly.

     

    1fwFvVK.png

     

     

     

    So then I thought I'd try to create two temp tables, one with declined transaction information and one with approved. THEN I'd combine the two:

     

     

    X4Lg68s.png

     

    The problem is if an afid + sid combination has only one type of transaction (approved OR declined - instead of both) I have not been able to figure out how to combine the tables.

     

    Any suggestions? I feel like there should be a fairly simple answer but I'm way over-complicating it. :X:

     

    Thank you! =)

    Nic

     

×
×
  • Create New...