CODEWITHSUNDEEP
sqlsql-serverselectsum
beginnerspirit
beginnerspirit

Reputation: 125

SUM SQL - Select Using With / Group by Year

I want to SUM the quantities of a purchased item but can not get the result using WITH (procedural and I was thinking simpler)

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;

This is what I get right know.

+-------------+--------+--------+
| "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" |
+-------------+--------+--------+

This is what I want to end up with

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

Upvotes: 1

Views: 49

Answers (2)

Atif
Atif

Reputation: 2210

Change your group by to this

GROUP BY ItemCode,YEAR(DocDate)

Upvotes: 1

Mureinik
Mureinik

Reputation: 312259

You're grouping by the DocDate instead of YEAR(DocDate) like you have in the select list:

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;

Upvotes: 1

Related Questions