LinuxQuestions.org

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

smaida 03-07-2007 08:21 AM

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

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


All times are GMT -5. The time now is 08:45 PM.