Nic. Posted October 31, 2014 Share Posted October 31, 2014 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted October 31, 2014 Share Posted October 31, 2014 You're using an inner join, and you should be using a full outer join.http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ Link to comment Share on other sites More sharing options...
Nic. Posted October 31, 2014 Author Share Posted October 31, 2014 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. Link to comment Share on other sites More sharing options...
Nic. Posted October 31, 2014 Author Share Posted October 31, 2014 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 Link to comment Share on other sites More sharing options...
Nic. Posted October 31, 2014 Author Share Posted October 31, 2014 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now