If you want to get a grand total as a separate row, use
GROUP BY ROLLUP
:
SELECT
Sum(Price) As Total,
Year([date]) As [year]
FROM
dbo.Expenses
WHERE
[date] Between '20170101' And '20191231'
And
Department = 'Grocery'
GROUP BY ROLLUP
(Year([date]))
;
GROUP BY (Transact-SQL) - SQL Server | Microsoft Docs[
^]
Example output:
Total | Year
------------
10 | 2017
20 | 2018
30 | 2019
60 | Null
The row with
Null
in the
Year
is the grand total.
Edit: To put the grand total in a separate column against each row, use:
WITH cte As
(
SELECT
Sum(Price) As Total,
Year([date]) As [year]
FROM
dbo.Expenses
WHERE
[date] Between '20170101' And '20191231'
And
Department = 'Grocery'
GROUP BY
Year([date])
)
SELECT
Total,
[year],
SUM(Total) OVER () As GrandTotal
FROM
cte
;