带日期的总和值 [英] Sum Value with Date
问题描述
Hy朋友,
我的查询有问题,我想用相同的日期求和(值)
I have a problem with my query, I wanna sum (value) with the same date
这个是我的查询
SELECT s.Name,
si.Name,
ai.TransactionDate,
sum(ai.StockQuantity)总计$
来自PurchaseTrx.ApItem ai
LEFT OUTER JOIN Inventory.Item i ON ai.ItemId = i.ItemId
LEFT OUTER JOIN Sales.SalesItem si ON i.ItemId = si.SalesItemId
LEFT OUTER JOIN Purchase.Supplier s ON ai.SupplierId = s.SupplierId
WHERE
ai.TransactionDate> ='2018-01-01'AND ai.TransactionDate< ='2019-05-03'
GROUP BY
s.name,
si.Name,
ai.TransactionDate
SELECT s.Name,
si.Name,
ai.TransactionDate,
sum(ai.StockQuantity) Total
FROM PurchaseTrx.ApItem ai
LEFT OUTER JOIN Inventory.Item i ON ai.ItemId = i.ItemId
LEFT OUTER JOIN Sales.SalesItem si ON i.ItemId = si.SalesItemId
LEFT OUTER JOIN Purchase.Supplier s ON ai.SupplierId = s.SupplierId
WHERE
ai.TransactionDate>='2018-01-01' AND ai.TransactionDate<='2019-05-03'
GROUP BY
s.name,
si.Name,
ai.TransactionDate
这就是我得到的结果
这是我想要的结果
有没有人可以ld帮帮我?
does anyone could help me ?
谢谢你的帮助
问候,
Steve Henry
Steve Henry
推荐答案
嗨Steve,
Hi Steve,
看看它是否有效。
SELECT s.Name,
si.Name,
min(ai.TransactionDate) as TransactionDate,
sum(ai.StockQuantity) Total
FROM PurchaseTrx.ApItem ai
LEFT OUTER JOIN Inventory.Item i ON ai.ItemId = i.ItemId
LEFT OUTER JOIN Sales.SalesItem si ON i.ItemId = si.SalesItemId
LEFT OUTER JOIN Purchase.Supplier s ON ai.SupplierId = s.SupplierId
WHERE
ai.TransactionDate>='2018-01-01' AND ai.TransactionDate<='2019-05-03'
GROUP BY
s.name,
si.Name,
cast(ai.TransactionDate as date)
最诚挚的问候,
将
这篇关于带日期的总和值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!