How do you group the SUM of rows by year in SQL

Question

SELECT          YEAR(OrderDate) 'Year', SUM(TotalDue)
FROM            Sales
GROUP BY        OrderDate
Order BY        OrderDate

How do I add each year together as ONE row? I wrote the query above, but the result still has the TotalDue by Year as individual rows. For example

Solution 1

You should GROUP BY Year, not OrderDate:

SELECT          YEAR(OrderDate), SUM(TotalDue)
FROM            Sales
GROUP BY        YEAR(OrderDate)
Order BY        OrderDate

Solution 2

You should group by the year of the order date and no the OrderDate itself.

SELECT          YEAR(OrderDate) AS `Year`, SUM(TotalDue)
FROM            Sales
GROUP BY        YEAR(OrderDate)
Order BY        Year

Comments

[sqluser]

Cannot use alias in GROUP because of query logical process

[Eda]

DId not work. Error: Each GROUP BY expression must contain at least one column that is not an outer reference.

[Eda]

THANK YOU Andrei. I created the RIGHT answer based on your input. Only the last line was eliminated, because it produced this Error message:Column "Sales.OrderDate" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

[Ameya Deshpande]

@Eda just provide alias for the year i.e. YEAR(OrderDate) 'YEAR' and in group by us YEAR(OrderDate) OR You can convert date format like DATEPART(yyyy,OrderDate) 'YEAR' and group by DATEPART(yyyy,OrderDate)