CODEWITHSUNDEEP
sqlsumaggregate-functions
Eda
Eda

Reputation: 208

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

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

enter image description here

Upvotes: 2

Views: 23761

Answers (5)

Oguzhan ARSLAN
Oguzhan ARSLAN

Reputation: 1

You need to re-group results

SELECT y, Sum(s) from 
  (
  SELECT          YEAR(OrderDate) as y , SUM(TotalDue) as s
    FROM            Sales
   GROUP BY        OrderDate
  )
group by y
Order BY y

Upvotes: 0

user3522371
user3522371

Reputation:

You have a problem in the GROUP BY statement because it operates on a selected column:

SELECT YEAR(OrderDate) theYear, SUM(Due) TotalDue
FROM Sales
GROUP BY theYear --Here you can either order by TotalDue or by theYear,
                 -- otherwise you will get the errors you mentioned

Upvotes: 4

sqluser
sqluser

Reputation: 5672

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

Upvotes: 0

Andrei Hirsu
Andrei Hirsu

Reputation: 696

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

Upvotes: 1

Uriil
Uriil

Reputation: 12628

You should GROUP BY Year, not OrderDate:

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

Upvotes: 4

Related Questions