获取计算（美元货币转换）共每个项目

Question

我有一个查询，得到每个项目的总和，但是当我改变SUM(d.AMT)到(SUM(d.AMT) * e.EXCHANGE_RATE) [USD Amount]总换算成美元我遇到的错误的：

Column 'EXCHANGE_RATE.EXCHANGE_RATE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

。有人可以修复我的查询吗？感谢大家！

SELECT d.ITEM_NO, h.ITEM_TYPE, h.ITEM_STATUS, d.CURRENCY,SUM(d.AMT)--(SUM(d.AMT) * e.EXCHANGE_RATE) [USD Amount] 
FROM 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 BY d.ITEM_NO, h.ITEM_TYPE, h.ITEM_STATUS, d.CURRENCY 

Answer 1

您需要前乘总和：

SELECT d.ITEM_NO, h.ITEM_TYPE, h.ITEM_STATUS, d.CURRENCY, 
     SUM(d.AMT * e.EXCHANGE_RATE) as [USD Amount] 
FROM 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 BY d.ITEM_NO, h.ITEM_TYPE, h.ITEM_STATUS, d.CURRENCY; 

但是，如果你想这对每个项目，然后简化SELECT和GROUP BY：

SELECT d.ITEM_NO, h.ITEM_TYPE, 
     SUM(d.AMT * e.EXCHANGE_RATE) as [USD Amount] 
FROM 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 BY d.ITEM_NO, h.ITEM_TYPE; 

Answer 2

变化 (SUM(d.AMT) * e.EXCHANGE_RATE) [USD Amount] 去 SUM(d.AMT * e.EXCHANGE_RATE) AS [USD Amount]