Jump to content

Nic.

Members
  • Posts

    6
  • Joined

  • Last visited

Profile Information

  • Gender
    Male
  • Location
    Phoenix area

Nic.'s Achievements

Newbie

Newbie (1/7)

0

Reputation

  1. My CASE and IF work perfectly when I remove the sum. (Dec_Sales values returned in the 35 rows adds up to the $ I was looking for). I didn't even think to test them that way ... Thanks for the speedy response! I'm off to learn how to properly use aggregate functions.
  2. 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
  3. 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
  4. 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
  5. I tried a full outer join late last night and it didn't work, but lets just blame the lateness of the hour. Let me go give it another whack and I'll post my results.
  6. 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. 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: 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. Thank you! =) Nic
×
×
  • Create New...