How to create query for search total sales previous year

Question

I have table named Sales.OrderValues that contain of 2 column, namely orderyear and val (total sales per day).

This is the record snippet (I cant show all of record because there are 830 rows)

I want to show the result like this

But, my output is different with my expected output.

As you can see, the expected output of prevtotalsales in 2008 is 618085.30. But, my output is 825169.29 (which is 208083.99 + 617085.30).

Below is my query

SELECT
   YEAR(D1.orderdate) AS orderyear,
   SUM(D1.val) AS curtotalsales,
   (
      SELECT
         SUM(D2.val) 
      FROM
         Sales.OrderValues D2 
      WHERE
         YEAR(D1.orderdate) > YEAR(D2.orderdate)
   )
   AS prevtotalsales 
FROM
   Sales.OrderValues D1 
GROUP BY
   YEAR(D1.orderdate);

How to show the SUM of totalsales at the previous year without adding the next year's totalsales?

What if you are missing a year? – Gordon Linoff Nov 20 '20 at 12:47 — Gordon Linoff, Nov 20 '20 at 12:47

score 2 · Answer 1 · answered Nov 20 '20 at 10:09

Basically, you want an equality condition in the WHERE clause of the subquery. This:

WHERE YEAR(D1.orderdate) > YEAR(D2.orderdate)

Should be:

WHERE YEAR(D1.orderdate) = YEAR(D2.orderdate) + 1

But it is much simpler and more efficient to just use lag():

SELECT
    YEAR(orderdate) AS orderyear,
    SUM(val) AS curtotalsales,
    LAG(SUM(val)) OVER(ORDER BY YEAR(orderdate))  AS prevtotalsales 
FROM Sales.OrderValues 
GROUP BY YEAR(orderdate)
ORDER BY orderyear

score 1 · Answer 2 · answered Nov 20 '20 at 10:10

You need to first SUM the values per year, and then use a cumulative SUM:

WITH Totals AS(
    SELECT YEAR(OV.orderdate) AS OrderYear
           SUM(OV.Val) AS YearSum
    FROM Sales.OrderValues OV
    GROUP BY YEAR(OV.orderdate))
SELECT OrderYear,
       YearSum,
       SUM(YearSum) OVER (ORDER BY OrderYear ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS PreviousCumulative
FROM Totals;

How to create query for search total sales previous year

2 Answers2