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

enter image description here

Please tag the RDMS you are using. – SQLMason May 16 '17 at 12:57 — SQLMason, May 16 '17 at 12:57

score 4 · Answer 1 · answered Nov 01 '14 at 09:30

4

You should GROUP BY Year, not OrderDate:

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

answered Nov 01 '14 at 09:30

Uriil

11,948
11
47
68

Cannot use alias in GROUP because of query logical process – sqluser Nov 01 '14 at 09:33
DId not work. Error: Each GROUP BY expression must contain at least one column that is not an outer reference. – Eda Nov 01 '14 at 09:33
1

@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)` – Ameya Deshpande Nov 01 '14 at 09:47

score 4 · Answer 2 · 2014-11-01T10:01:13.327

4

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

edited Nov 01 '14 at 10:01

answered Nov 01 '14 at 09:55

@VDohnal it will work as long as the GROUP BY is performed on a selected column, which is my case. – Nov 01 '14 at 10:08
@VDohnal yes, you are right (i even tried something else similar). My answer is wrong, you are right. Thank you for correcting me – Nov 01 '14 at 10:17
But as I see it works in MySQL and Postgress, so it is not so bad after all. http://stackoverflow.com/questions/3841295/sql-using-alias-in-group-by – Vojtěch Dohnal Nov 01 '14 at 10:22
@VDohnal Thank you again, but you are right, on Oracle it does not work. One way to make this work on Oracle is to select `OrderDate` too to use it for the `GROUP BY`. But the questioner needs 2 columns as a result, not 3, so we create what I said as a view, then from the view of 3 columns we pick the 2 columns he asked. Note that on Oracle `YEAR` is not accepted either. Thank you again for the useful comment. – Nov 01 '14 at 10:26

score 1 · Answer 3 · answered Nov 01 '14 at 09:32

1

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

answered Nov 01 '14 at 09:32

Andrei Hirsu

686
4
11

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. – Eda Nov 01 '14 at 09:38

sqluser · Answer 4 · 2014-11-01T09:43:05.347

0

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

edited Nov 01 '14 at 09:43

answered Nov 01 '14 at 09:31

sqluser

5,502
7
36
50

Oguzhan ARSLAN · Answer 5 · 2014-11-01T10:29:05.610

0

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

edited Nov 01 '14 at 10:29

answered Nov 01 '14 at 09:39

Oguzhan ARSLAN

1
1

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

5 Answers5