时间戳

首页 > 解决方案 > SUM SQL - 按年份选择使用/分组

sql - SUM SQL - 按年份选择使用/分组

问题描述

我想对所购买商品的数量求和，但无法使用 WITH 得到结果（程序上，我想更简单）

WITH Purchases (ItemCode, Quantity, DocDate)  
AS  
(  
    SELECT ItemCode, Quantity, OPOR.DocDate
    FROM OPOR INNER JOIN POR1 ON OPOR.DocEntry = POR1.DocEntry 
    WHERE Canceled = 'N'
    GROUP BY OPOR.DocDate,ItemCode,QuantitY
)  
SELECT ItemCode, SUM(Quantity) AS Qty, YEAR(DocDate) AS Year
FROM Purchases
WHERE DocDate > DATEADD(YEAR,-3,GETDATE()) AND ItemCode = '01.BEE.05'
GROUP BY ItemCode,DocDate
ORDER BY ItemCode;

这就是我所知道的。

+-------------+--------+--------+
| "ItemCode"  | "Qty"  | "Year" |
+-------------+--------+--------+
| "01.BEE.05" | "3000" | "2018" |
+-------------+--------+--------+
| "01.BEE.05" | "3000" | "2019" |
+-------------+--------+--------+
| "01.BEE.05" | "3000" | "2019" |
+-------------+--------+--------+
| "01.BEE.05" | "3000" | "2019" |
+-------------+--------+--------+
| "01.BEE.05" | "3000" | "2019" |
+-------------+--------+--------+
| "01.BEE.05" | "3000" | "2019" |
+-------------+--------+--------+
| "01.BEE.05" | "3000" | "2019" |
+-------------+--------+--------+
| "01.BEE.05" | "3000" | "2020" |
+-------------+--------+--------+
| "01.BEE.05" | "3000" | "2020" |
+-------------+--------+--------+
| "01.BEE.05" | "9000" | "2020" |
+-------------+--------+--------+
| "01.BEE.05" | "3000" | "2020" |
+-------------+--------+--------+

这就是我想要结束的

+-------------+---------+--------+
| "01.BEE.05" | "3000"  | "2018" |
+-------------+---------+--------+
| "01.BEE.05" | "18000" | "2019" |
+-------------+---------+--------+
| "01.BEE.05" | "18000" | "2020" |
+-------------+---------+--------+

标签： sqlsql-serverselectsum

解决方案

您按选择列表中的DocDate而不是分组：YEAR(DocDate)

WITH Purchases (ItemCode, Quantity, DocDate)  
AS  
(  
    SELECT ItemCode, Quantity, OPOR.DocDate
    FROM OPOR INNER JOIN POR1 ON OPOR.DocEntry = POR1.DocEntry 
    WHERE Canceled = 'N'
    GROUP BY OPOR.DocDate,ItemCode,QuantitY
)  
SELECT ItemCode, SUM(Quantity) AS Qty, YEAR(DocDate) AS Year
FROM Purchases
WHERE DocDate > DATEADD(YEAR,-3,GETDATE()) AND ItemCode = '01.BEE.05'
GROUP BY ItemCode, YEAR(DocDate)
-- Here -----------^
ORDER BY ItemCode;

推荐阅读