Jump to content

Will an inline Query work?


Nic.

Recommended Posts

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

 

Link to comment
Share on other sites

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

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...