I am doing a query using this syntax:
我正在使用以下语法进行查询:
SELECT itemid, description, store, SUM(localcurrencyamount * exchangerate) AS 'US Currency Amount'
FROM item;
It is retuning 1 out of 4 rows where it is showing the correct first row itemid, description, and store, but it is adding all of both columns localcurrencyamount and exchangerate multiplication into one field. So my result should be, in row itemid 1, 7156.4047, but I am getting a value of 8426.7303; which is the total value for the new column US Currency Amount of ALL rows (1 through 4).
它正在重新调整4行中的1行,它显示正确的第一行itemid,description和store,但它将所有两列都添加到localcurrencyamount并将乘法交换到一个字段中。所以我的结果应该是,在行itemid 1,7156.4047,但我得到的值是8426.7303;这是所有行(1到4)的新列美国货币金额的总值。
What is wrong with this syntax that is causing all of that data to be 'lumped' into one field instead over 4? When I ran:
这种语法有什么问题导致所有数据被“集中”到一个字段而不是4个字段?当我跑:
SELECT itemid, description, store FROM item;
It returned all of the information from 4 rows separately, but when I added SUM(localcurrencyamount * exchangerate) AS 'US Currency Amount' is when the data lumped together into just the first row.
它分别从4行返回所有信息,但是当我添加SUM(localcurrencyamount * exchangerate)时,AS'美国货币金额'是指数据汇总到第一行时。
Thanks for the help!
谢谢您的帮助!
2 个解决方案
#1
3
People in comments seem to think you need GROUP BY, but it sounds like in your case that is not correct. SUM() means just that: it sums up (adds up) rows. You don't want that. You want:
评论中的人似乎认为你需要GROUP BY,但听起来你的情况并不正确。 SUM()意味着:它总结(累加)行。你不希望这样。你要:
SELECT itemid, description, store, (localcurrencyamount * exchangerate) AS 'US Currency Amount'
FROM item;
#2
0
If you want to get the result for each record separately, don't use SUM. SUM is an aggregate function that works on a group of records. Use just:
如果要分别获取每个记录的结果,请不要使用SUM。 SUM是一个对一组记录起作用的聚合函数。使用只:
SELECT itemid, description, store, (localcurrencyamount * exchangerate) AS 'US Currency Amount'
FROM item;
#1
3
People in comments seem to think you need GROUP BY, but it sounds like in your case that is not correct. SUM() means just that: it sums up (adds up) rows. You don't want that. You want:
评论中的人似乎认为你需要GROUP BY,但听起来你的情况并不正确。 SUM()意味着:它总结(累加)行。你不希望这样。你要:
SELECT itemid, description, store, (localcurrencyamount * exchangerate) AS 'US Currency Amount'
FROM item;
#2
0
If you want to get the result for each record separately, don't use SUM. SUM is an aggregate function that works on a group of records. Use just:
如果要分别获取每个记录的结果,请不要使用SUM。 SUM是一个对一组记录起作用的聚合函数。使用只:
SELECT itemid, description, store, (localcurrencyamount * exchangerate) AS 'US Currency Amount'
FROM item;