LinuxQuestions.org - SQL Query - sum / group by one year periods

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - SQL Query - sum / group by one year periods (https://www.linuxquestions.org/questions/programming-9/sql-query-sum-group-by-one-year-periods-535367/)

SQL Query - sum / group by one year periods

Hello all,

I'm trying to solve a problem in which I need to select the sum of a field grouped by
1 year periods starting from the earliest date. I'm sorry if this is not a good explanation but
I don't really know where to start. The database is Oracle.

Simplified Table structure:
--------------------------
TABLE A
SOL_NUM VARCHAR2

TABLE B
SOL_NUM VARCHAR2
AWARD_DATE DATE
AWARD_QUANTITY NUMBER
--------------------------

WHERE A.SOL_NUM = B.SOL_NUM

NEED TO GET THE SUM OF AWARD_QUANTITY PER 1 YEAR PERIOD
STARTING WITH THE EARLIEST AWARD_DATE

Do I need to select the first award date and then do individual queries for each 1 year period or is there some way to get all info in one query.

In all cases the maximum number of 1 year periods is 5. So if the earliest date in the AWARD_DATE field is 01/01/05 I would need to select SUM(AWARD_QUANTITY) for

01/01/05 to 01/01/06
01/02/06 to 01/01/07
01/02/07 to 01/01/08
01/02/08 to 01/01/09
01/02/09 to 01/01/010

Thank you for your help.
-Shawn

I would do this in PostgreSQL... Not sure if Oracle has the EXTRACT function...

Code:

SELECT EXTRACT(year FROM b.award_date) as award_year

      SUM(b.award_quantity) as award_quantity

FROM  table_b AS b

GROUP BY EXTRACT(year FROM b.award_date)

ORDER BY EXTRACT(year FROM b.award_date)

Thanks for your response -- It seems easy enough. I'll give it a shot and let you know.

-Shawn