Home » SQL & PL/SQL » SQL & PL/SQL » Summing up data by quarters
Summing up data by quarters [message #2786] Tue, 13 August 2002 10:09 Go to next message
Sue
Messages: 49
Registered: May 2000
Member
Please Help!!
I have a database table where I have labor information by months. I need to group them by Quarters(three months). In one statement how do I do that?
Sue
Sample of data

area year month LABOR EMP unemp
000049 1998 01 1043617 1007439 36178
000049 1998 02 1042425 1002409 40016
000049 1998 03 1040039 1001507 38532
000049 1998 04 1043405 1007799 35606
000049 1998 05 1050836 1014239 36597
000049 1998 06 1075807 1029079 46728
000049 1998 07 1080241 1035986 44255
000049 1998 08 1080201 1034210 45991
000049 1998 09 1074251 1034896 39355
000049 1998 10 1081334 1040006 41328
000049 1998 11 1080946 1041945 39001
000049 1998 12 1077143 1040717 36426
Re: Summing up data by quarters [message #2790 is a reply to message #2786] Tue, 13 August 2002 11:05 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
group by to_char(to_date(year || month, 'yyyymm'), 'q')
 
sql>select year, month, to_char(to_date(year || month, 'yyyymm'), 'q'), amount
  2   from t;
 
     YEAR MO T    AMOUNT
--------- -- - ---------
     1998 01 1         2
     1998 02 1         4
     1998 03 1         6
     1998 04 2         8
     1998 05 2        10
     1998 06 2        12
     1998 07 3        14
     1998 08 3        16
     1998 09 3        18
     1998 10 4        20
     1998 11 4        22
     1998 12 4        24
 
12 rows selected.
 
sql>select to_char(to_date(year || month, 'yyyymm'), 'q') quarter, sum(amount)
  2    from t
  3   group by to_char(to_date(year || month, 'yyyymm'), 'q');
 
Q SUM(AMOUNT)
- -----------
1          12
2          30
3          48
4          66
Re: Summing up data by quarters [message #2792 is a reply to message #2786] Tue, 13 August 2002 12:17 Go to previous message
Sue
Messages: 49
Registered: May 2000
Member
Todd
Please bear with me for a few minutes. The period column also has 13 which is Annual Average and I am not using it for the calculation of the quarter. how do i write the sql so that can be ignored?
The data type for the year and month fields are all char type. Does it make this any more difficult??

I also have another look up table which is related to this data table. Sample data from the look up table:
year per_type period perioddesc
Sue
1995 01 01 Annual
1995 02 01 Quarter 1
1995 02 02 Quarter 2
1995 02 03 Quarter 3
1995 02 04 Quarter 4
1995 03 01 January
1995 03 02 February
1995 03 03 March
1995 03 04 April
1995 03 05 May
1995 03 06 June
1995 03 07 July
1995 03 08 August
1995 03 09 September
1995 03 10 October
1995 03 11 November
1995 03 12 December
1995 03 13 1995 Annual Average
Previous Topic: Use of Commit after DML statement
Next Topic: COPY command in PL/SQL
Goto Forum:
  


Current Time: Tue Apr 23 04:54:19 CDT 2024