OraFAQ Forum: SQL & PL/SQL » Re: Summing up data by quarters

Home » SQL & PL/SQL » SQL & PL/SQL » Re: Summing up data by quarters

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Re: Summing up data by quarters [message #2810]

Wed, 14 August 2002 09:00

Sue
Messages: 49
Registered: May 2000

Member

Todd
I want to clarify one more time. I am starting from the scratch to understand group by expression. I went and changed my lookup table to accomodate quarters. so my data table (I am showing a sample here)looks like this:
area periodyear period 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

My lookup table looks like this now:

periodyear periodtype period periodind perioddesc
1998 01 01 01 Annual
1998 02 01 01 Quarter 1
1998 02 01 02 Quarter 1
1998 02 01 03 Quarter 1
1998 02 02 04 Quarter 2
1998 02 02 05 Quarter 2
1998 02 02 06 Quarter 2
1998 02 03 07 Quarter 3
1998 02 03 08 Quarter 3
1998 02 03 09 Quarter 3
1998 02 04 10 Quarter 4
1998 02 04 11 Quarter 4
1998 02 04 12 Quarter 4
1998 03 01 01 January
1998 03 02 02 February
1998 03 03 03 March
1998 03 04 04 April
1998 03 05 05 May
1998 03 06 06 June
1998 03 07 07 July
1998 03 08 08 August
1998 03 09 09 September
1998 03 10 10 October
1998 03 11 11 November
1998 03 12 12 December
1998 03 13 13 Annual Average

Now looking at these two tables, tell me how i can show sum of labor and emp and unemp data by quarters for the year 1998?
Thank you in advance
Sue

Report message to a moderator

Re: Summing up data by quarters [message #2815 is a reply to message #2810]

Wed, 14 August 2002 09:34

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

First off Sue, you don't even need the lookup table. Everything in the lookup table can be generated dynamically by Oracle at query time.

The quarters are easy - we have already derived a numerical quarter in the query, so for the description we just prepend 'Quarter ' to the value.

The months are easy - we just use built-in functions in Oracle that return a month name based on a date.

The Annual entries can be shown by examining the data and using DECODE.

So, why have a table containing data that can be generated dynamically? It is just a lot of extra work that brings no benefit.

On to the query:

sql>select to_char(to_date(period, 'mm'), 'q') quarter,
  2         'Quarter ' || to_char(to_date(period, 'mm'), 'q') description,
  3         sum(labor), sum(emp), sum(unemp)
  4    from t
  5   where periodyear = '1998'
  6   group by to_char(to_date(period, 'mm'), 'q');
 
Q DESCRIPTI SUM(LABOR)  SUM(EMP) SUM(UNEMP)
- --------- ---------- --------- ----------
1 Quarter 1    3126081   3011355     114726
2 Quarter 2    3170048   3051117     118931
3 Quarter 3    3234693   3105092     129601
4 Quarter 4    3239423   3122668     116755

See how the description column was built on the fly (no lookup table). Here's an example showing how the months can be displayed automatically:

sql>select periodyear, period, to_char(to_date(period, 'mm'), 'Month') month
  2    from t;
 
PERI PE MONTH
---- -- ---------
1998 01 January
1998 02 February
1998 03 March
1998 04 April
1998 05 May
1998 06 June
1998 07 July
1998 08 August
1998 09 September
1998 10 October
1998 11 November
1998 12 December

Hope this helps you...

Report message to a moderator

Previous Topic:	SQL Plus environment setting
Next Topic:	problem with dates

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 14:48:41 CDT 2024