sql query for Last 5 years and group by?

Question

I'm new to SQL, I'm working on DB2, I need to get the sum of x and y of the last 5 years for a id.

the following is the query up to which i've acheived so far, but, i need to get only the last 5 yaers, this gets me the data for all the years. I've tried to add top 5 for the sql statement, it seems to not work, it shows some error. How do I modify this query to fetch only the last 5 years data?

select (sum(X) + sum(Y)) as tot,some_date
from table where fkid = 1 
group by year order by year desc;

The following the data format that I'm expecting.

Total Year
552,074.78 2012-01-01 466,283.62 2011-01-01 640,813.37 2010-01-01 721,182.28 2009-01-01 719,676.05 2008-01-01

The error that i'm getting is as follows

Error: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=5;VALUE ( CONCAT || / - + FROM INTO * , \ AS . YEAR, DRIVER=3.58.81 SQLState: 42601 ErrorCode: -104 Error: DB2 SQL Error: SQLCODE=-514, SQLSTATE=26501, SQLERRMC=SQL_CURLH200C1, DRIVER=3.58.81 SQLState: 26501 ErrorCode: -514

Edit: the column has date type in it, and the column name is not year, for example sake i've modified it to have some_year.

If it shows some error, you'll need to do something to fix your query, obviously. Begin by stating your DB2 platform and version. Also include with your query sample data and the desired output. — mustaccio

Gordon Linoff Gordon Linoff · Accepted Answer · 2013-08-14T22:28:56

The problem with your query is easy (assuming this is the only problem). year is a reserved word, so surround it by double quotes:

select (sum(X) + sum(Y)) as tot, "year"
from table
where fkid = 1 
group by "year"
order by "year" desc;

Getting the last five years is a different matter. Based on the year column, you could do:

select (sum(X) + sum(Y)) as tot, "year"
from table
where fkid = 1 and year >= year(current timestamp) - 5
group by "year"
order by "year" desc;

Or you might actually have another column that has a date time that you can use instead of "year".

EDIT:

If you have a column called dt that is the date, you would use:

select (sum(X) + sum(Y)) as tot, year(dt) as "year"
from table
where fkid = 1 and dt >= CURRENT DATE - 5 years and dt <= CURRENT DATE
group by year(dt)
order by year(dt) desc;

If you want only one row with the sum, leave out the group by clause.

sql query for Last 5 years and group by?

1 Answers