SQL Server Sum column total by ID for each row for that ID

jaql4c8m  于 2023-06-21  发布在  其他
关注(0)|答案(1)|浏览(143)

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
ORDERTOTALITEMWEIGHT
1973180
197318600
197318800
197318196
1973180
19731814

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.

ma8fv8wu

ma8fv8wu1#

You can use SUM(<expr>) OVER(...) . For example:

SELECT
  Transactions.Order, 
  ItemFile.Weight * TransactionItems.QTY AS TotalItemWeight,
  sum(ItemFile.Weight * TransactionItems.QTY) 
    over(partition by Transactions.Order) as orderweight
FROM ItemFile 
INNER JOIN TransactionItems 
INNER JOIN Transactions 
  ON TransactionItems.Order = Transactions.CNTR 
  ON ItemFile.NUM = TransactionItems.ITEM

相关问题