Oracle year, quarter, month, week, day, grouping Java code table 1: Date (exportDate) Quantity (amount) -------------- ----------- 14-2 month-08 20-3 month-08 2 14-4 month-08 6-6 month-08 75 24-10 month-09 23-11 month-09 45 04-8 month-10 5 04-9 month- 10 44 04-10-10 88 Note: for more intuitive display, the following queries are sorted by corresponding groups. select to_char (exportDate, 'yyyy'), sum (amount) from table1 group by to_char (exportDate, 'yyyy '); number of years ----------------------------- 2009 68 2010 137 2008 2. select to_char (exportDate, 'yyyy-mm'), sum (amount) from table1 group by to_char (exportDate, 'yyyy-mm') order by to_char (exportDate, 'yyyy-mm '); number of months ----------------------------- 2008-02 20 2008-03 2 2008-04 6 2008-06 75 2009-10 23 2009-11 45 2010-08 5 2010-09 44 2010-10 88 3. select to_char (exportDate, 'yyyy-Q'), sum (amount) from table1 group by to_char (exportDate, 'yyyy-Q') order by to_char (exportDate, 'yyyy-Q'); quarterly quantity ------------------------------ 2008-1 22 2008-2 81 2009-4 68 2010-3 49 2010-4 88 4. select to_char (exportDate, 'yyyy-iw'), sum (amount) from table1 group by to_char (exportDate, 'yyyy-iw') order by to_char (exportDate, 'yyyy-iw '); weekly quantity ------------------------------ 2008-07 20 2008-11 2 2008-16 6 2008-24 75 2009-43 23 2009-46 45 2010-31 5 2010-35 44 2010-40 88 supplement: grouping by quarter also has a relatively stupid method: select to_char (exportDate, 'yyyy'), sum (decode (to_char (exportDate, 'mm'), '01', amount, '02', amount, '03', amount, 0) as first season, sum (decode (to_char (exportDate, 'mm'), '04 ', amount, '05 ', amount, '06', amount, 0) as second season, sum (decode (to_char (exportDate, 'mm'), '07', amount, '08 ', amount, '09', amount, 0) as Quarter 3, sum (decode (to_char (exportDate, 'mm'), '10', amount, '11', amount, '12', amount, 0) as quarter 4 from table1 group by to_char (exportDate, 'yyyy '); season 1 season 2 Season 4 Season 4 -------------------------------------------------- 2009 0 0 0 68 2010 0 49 88 2008 22 81 0 0