SQL Query

smaida · 03-07-2007, 08:21 AM

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

fukawi2 · 03-08-2007, 06:00 PM

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)

smaida · 03-09-2007, 08:10 AM

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

-Shawn