英文:
Sum column total by ID for each row for that ID
问题
我尝试通过将订单中所有商品的重量相加来获取订单的总重量，该重量位于单独的列中，该列对应订单的每个条目重复显示订单重量。
当前查询：
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
| 订单 | 商品总重量 |
|---|---|
| 197318 | 0 |
| 197318 | 600 |
| 197318 | 800 |
| 197318 | 196 |
| 197318 | 0 |
| 197318 | 14 |
尝试实现的目标：
| 订单 | 商品总重量 | 订单重量 |
|---|---|---|
| 197318 | 0 | 1610 |
| 197318 | 600 | 1610 |
| 197318 | 800 | 1610 |
| 197318 | 196 | 1610 |
| 197318 | 0 | 1610 |
| 197318 | 14 | 1610 |
我尝试过使用 UNION ALL，但它只是添加了另一行，显示订单总计。我将加载这些数据到的系统需要查看具有总重量的单独列。由于文件中每个订单可能有多行，因此“订单重量”将在对应该订单的每行中重复显示。
英文:
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
得分: 2
你可以使用 SUM(<expr>) OVER(...)。例如：
选择
交易订单，
ItemFile.Weight * TransactionItems.QTY 作为总物品重量，
按交易订单分区的总和(ItemFile.Weight * TransactionItems.QTY) as orderweight
从ItemFile
内连接TransactionItems
内连接Transactions
在TransactionItems.Order = Transactions.CNTR
在ItemFile.NUM = TransactionItems.ITEM
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论