Two criteria in a field to get one result

Question

I want to get a result of two criteria on the same field as a column. The DB has two different currency records one USD and the other TRY in the same field of a table. So, I want to get a result as finding both currencies rows one by one and divide TRY rate of exchange as USD at the same time when we get TRY result of corresponding to USD.

I want to add the script that I am trying to get here that in this code:

SELECT e.Gk_2, f.ad, sum(g.sf_miktar) as Qty
from Stok00 e  
LEFT OUTER JOIN Gecoust f on e.Gk_2 = f.kod 
left outer join stok29t g on e.kod = g.kod
where e.kod like 'SAC SIL%' and f.Evrakno = 'STKGK2' 
group by e.GK_2, f.ad 

example:

(Select sum(stock29.amount) from stock29 where stock29.rate = 'USD')
(Select sum(stock29.amount / 1.8) from stock29 where stock29.rate = 'TRY')

Answer 1

If I understood you correctly then your query should look something like this (I'm just adding a field to your query assuming that it works):

SELECT e.Gk_2, f.ad, sum(g.sf_miktar) as Qty,
SUM(CASE WHEN stocks29.rate = 'USD' THEN stocks29.amount
         WHEN stocks29.rate = 'TRY' THEN stocks29.amount / 1.8 END) As Amount
from Stok00 e  
LEFT OUTER JOIN Gecoust f on e.Gk_2 = f.kod 
left outer join stok29t g on e.kod = g.kod
where e.kod like 'SAC SIL%' and f.Evrakno = 'STKGK2' 
group by e.GK_2, f.ad 

I also assumed that neither stocks29.rate nor stocks29.amount would ever be NULL. If this is not the case, then they should be wrapped in ISNULL() function appropriately.