finding highest total price and converting

So I have to use a query where I list the trade id stock id and the total price converted to us dollars where it is the highest price total.

SELECT
tr.trade_id, tr.stock_id, round(tr.price_total * con.exchange_rate,2)
as "US Dollars"

from trade tr
JOIN stock_exchange se
on se.STOCK_EX_ID = tr.STOCK_EX_ID

JOIN currency curr
on curr.CURRENCY_ID = se.currency_id

JOIN conversion con
on con.from_CURRENCY_ID = curr.CURRENCY_ID

WHERE (tr.PRICE_TOTAL) = (Select Max(price_total) from trade) and curr.name =
'Dollar' and tr.stock_ex_id is not NULL
group by tr.trade_id, tr.stock_id, round(tr.price_total), tr.price_total,
round(tr.price_total * con.exchange_rate,2);


Trade (trade_id PK, stock_id FK2, transaction_time, shares, stock_ex_id FK1,price_total)

Stock-exchange( stock_ex_id PK, name, symbol, currency_id FK1)

conversion( from_currency_id PK, to_currency_id)

currency ( currency_id PK, name, symbol

expected output should be -

trade_id - 1 stock_id 1, price (non conversion) (225000000)


I'm not sure why in my output i get nothing. Any suggestions to fix this? Sorry if i did not format the question right

Some sample data and expected output would be helpful here.
– Tim Biegeleisen
2 days ago

@TimBiegeleisen sort of cant really post the ERD but what sample data would you need?
– Ohboyy
2 days ago

Give enough data so that we can see what is going on. Given that you have 4 tables in that query, it's kind of hard to synthesize/imagine data for all tables.
– Tim Biegeleisen
2 days ago

@TimBiegeleisen dont know if that helped
– Ohboyy
2 days ago

2 Answers
2

But you could start by seeing what your select max query returns, and then filtering down the trade table by that value. Trace this though all of your tables to see where the data on your trade table isn't on the others.

OR put you curr.name criteria up with the join and specify a LEFT JOIN for the last two tables to see if the data is missing like below:

SELECT tr.trade_id
,tr.stock_id
,round(tr.price_total * con.exchange_rate, 2) AS "US Dollars"
FROM trade tr
LEFT JOIN stock_exchange se
ON se.STOCK_EX_ID = tr.STOCK_EX_ID
LEFT JOIN currency curr
ON curr.CURRENCY_ID = se.currency_id
AND curr.name = 'Dollar'
LEFT JOIN conversion con
ON con.from_CURRENCY_ID = curr.CURRENCY_ID
WHERE (tr.PRICE_TOTAL) = (SELECT Max(trm.price_total) FROM trade trm)
AND tr.stock_ex_id IS NOT NULL


Okay i tried that where tr.stock_ex_id is null and the highest price total has a NULL tr.stock_ex_id but when i do IS NOT NULL still empty answer
– Ohboyy
2 days ago

Make sure the select max doesn't have a null stock ID
– Wyatt Shipman
yesterday

It could be other things as well, but one spot to look is this

You say WHERE tr.PRICE_TOTAL = (Select Max(price_total) from trade)
AND curr.name = 'Dollar'

If the trade with the Maximum price_total is not in Dollars, then you'll get nothing.

You need to either change your "Select Max(price_total) from trade" to get maximum values for Dollar trades only, or else get all the valid trades in a subquery and then get the maximum value from that.

After comment below - to debug - run "Select * from trade where price_total = (Select Max(price_total) from trade)" to get the valid trade record(s). Then look at them - they are going to fail somewhere - no STOCK_EX_ID on trade, or one of the other joins fails - you need to start from known data and go from there.

Hmm apparently i dont need to add the curr,name = 'Dollar' . I took it out but still empty answer
– Ohboyy
2 days ago

In that case, next most likely cause is that your top trade has no stock_ex_id - that wouldn't show either
– DancingFool
2 days ago

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.