I trying to get the total weight for an order by summing all of the weights of the items in a separate column that repeats the order weight for each for of the order. I was able to get the total weight of each item in the order, qty * item weight, but I need a separate column that sums all of the item weights for the order.
Current query:
SELECT Transactions.Order, ItemFile.Weight * TransactionItems.QTY AS TotalItemWeight
FROM ItemFile INNER JOIN TransactionItems INNER JOIN Transactions ON TransactionItems.Order = Transactions.CNTR ON ItemFile.NUM = TransactionItems.ITEM
ORDER | TOTALITEMWEIGHT |
---|---|
197318 | 0 |
197318 | 600 |
197318 | 800 |
197318 | 196 |
197318 | 0 |
197318 | 14 |
Trying to achieve:
| ORDER | TOTALITEMWEIGHT | ORDERWEIGHT |
| ------------ | ------------ | ------------ |
| 197318 | 0 | 1610 |
| 197318 | 600 | 1610 |
| 197318 | 800 | 1610 |
| 197318 | 196 | 1610 |
| 197318 | 0 | 1610 |
| 197318 | 14 | 1610 |
I tried a union all but it just adds another row with the order total. The system I'm loading this data into needs to look at a separate column with the total weight. Since the file will have multiple rows per order, the ORDERWEIGHT will be repeated for every line that corresponds to that order.
1条答案
按热度按时间ma8fv8wu1#
You can use
SUM(<expr>) OVER(...)
. For example: