LinuxQuestions.org
Visit Jeremy's Blog.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices



Reply
 
Search this Thread
Old 03-07-2007, 09:21 AM   #1
smaida
Member
 
Registered: Apr 2004
Location: Richmond, VA - USA
Distribution: Debian
Posts: 62

Rep: Reputation: 15
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
 
Old 03-08-2007, 07:00 PM   #2
fukawi2
Member
 
Registered: Oct 2006
Location: Melbourne, Australia
Distribution: ArchLinux, ArchServer, Fedora, CentOS
Posts: 448

Rep: Reputation: 34
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)
 
Old 03-09-2007, 09:10 AM   #3
smaida
Member
 
Registered: Apr 2004
Location: Richmond, VA - USA
Distribution: Debian
Posts: 62

Original Poster
Rep: Reputation: 15
Thanks for your response -- It seems easy enough. I'll give it a shot and let you know.

-Shawn
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Error in SQL Query.. gobi_dgm Linux - Software 1 02-03-2007 10:41 AM
SQL query question Kamikazee Programming 2 10-31-2006 11:27 AM
Query SQL error gabsik Linux - Software 37 07-22-2006 03:34 AM
Get the sum of accounts id's that have the same group Smsem Linux - General 2 12-03-2004 03:54 AM
MySQL SUM Query zimba Programming 3 03-28-2003 10:55 AM


All times are GMT -5. The time now is 12:43 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration