SQL14 Commulative SUM of Sales Year wise

Data is given table, we have date along with the sales. We need to calculate commulative sum for every year given in table.

below are the various ways to perform this task

Data in Table 

Year	Sales
2019-09-01	10
2019-10-01	60
2019-11-01	20
2019-12-01	10
2020-01-01	30
2020-02-01	20
2020-03-01	80
2021-01-01	70
2021-02-01	30
2021-03-01	10

Expected Result

year	sales	Sum
2019-09-01	10	10
2019-10-01	60	70
2019-11-01	20	90
2019-12-01	10	100
2020-01-01	30	30
2020-02-01	20	50
2020-03-01	80	130
2021-01-01	70	70
2021-02-01	30	100
2021-03-01	10	110

Table Insert Query

SELECT *
INTO SQL14
FROM (
	SELECT '2019-09-01' AS MONTH
		, 10 AS SALES
	
	UNION ALL
	
	SELECT '2019-10-01' AS MONTH
		, 60 AS SALES
	
	UNION ALL
	
	SELECT '2019-11-01' AS MONTH
		, 20 AS SALES
	
	UNION ALL
	
	SELECT '2019-12-01' AS MONTH
		, 10 AS SALES
	
	UNION ALL
	
	SELECT '2020-01-01' AS MONTH
		, 30 AS SALES
	
	UNION ALL
	
	SELECT '2020-02-01' AS MONTH
		, 20 AS SALES
	
	UNION ALL
	
	SELECT '2020-03-01' AS MONTH
		, 80 AS SALES
	
	UNION ALL
	
	SELECT '2021-01-01' AS MONTH
		, 70 AS SALES
	
	UNION ALL
	
	SELECT '2021-02-01' AS MONTH
		, 30 AS SALES
	
	UNION ALL
	
	SELECT '2021-03-01' AS MONTH
		, 10 AS SALES
	) t

--oracle
SELECT MONTH
	, SALES
	, (
		SELECT SUM(SALES)
		FROM SQL14 I
		WHERE I.MONTH <= O.MONTH
			AND EXTRACT(YEAR FROM TO_DATE(O.MONTH, 'YYYY-MM-DD')) = EXTRACT(YEAR FROM TO_DATE(I.MONTH, 'YYYY-MM-DD'))
		) AS YEARTODATE_SALE
FROM SQL14 O;

--SQLServer Solution 1
SELECT year(month)
	, sum(sales) OVER (
		PARTITION BY year(month) ORDER BY month
		)
	, *
FROM sql14

--SQLServer Solution 2
SELECT MONTH
	, SALES
	, (
		SELECT SUM(SALES)
		FROM SQL14 I
		WHERE I.MONTH <= O.MONTH
			AND YEAR(O.MONTH) = YEAR(I.MONTH)
		) AS YEARTODATE_SALE
FROM SQL14 O;

--SQLServer Solution 3
WITH cte
AS (
	SELECT *
		, year(month) y
	FROM sql14
	)
SELECT DISTINCT *
FROM SQL14 c
CROSS APPLY (
	SELECT DISTINCT sum(sales) AS total
	FROM SQL14
	WHERE (c.month) >= (month)
		AND year(c.month) = year(month)
		--group by year(month)
	) t

--SQLServer Solution 4
SELECT t1.month
	, year(t1.month)
	, t1.sales
	, sum(t2.sales)
FROM sql14 t1
JOIN sql14 t2 ON (t1.month) >= (t2.month)
	AND year(t1.month) = year(t2.month)
GROUP BY t1.month
	, t1.sales
	, year(t1.month)
ORDER BY 1
	, 2

Tags- SQL Excercise, Requirement solution, Practice, Query Logic, Assignments, SQL Practice