sql - Get computed (USD currency conversion) total of each item -

i have query gets sum of each item, when change sum(d.amt) (sum(d.amt) * e.exchange_rate) [usd amount] convert total usd encounter error:

column 'exchange_rate.exchange_rate' invalid in select list because not contained in either aggregate function or group clause.

. can please fix query? everyone!

select d.item_no, h.item_type, h.item_status, d.currency,sum(d.amt)--(sum(d.amt) * e.exchange_rate) [usd amount] item_hdr h join item_det d on h.item_no = d.item_no join exchange_rate e on d.currency = e.currency_from group d.item_no, h.item_type, h.item_status, d.currency 

you need multiply before sum:

select d.item_no, h.item_type, h.item_status, d.currency,        sum(d.amt * e.exchange_rate) [usd amount] item_hdr h join      item_det d      on h.item_no = d.item_no join      exchange_rate e       on d.currency = e.currency_from group d.item_no, h.item_type, h.item_status, d.currency; 

however, if want each item, simplify select , group by:

select d.item_no, h.item_type,        sum(d.amt * e.exchange_rate) [usd amount] item_hdr h join      item_det d      on h.item_no = d.item_no join      exchange_rate e       on d.currency = e.currency_from group d.item_no, h.item_type;