Home » SQL & PL/SQL » SQL & PL/SQL » sql query to get avg value (Oracle 11g)
sql query to get avg value [message #632018] Fri, 23 January 2015 07:31 Go to next message
thelearner
Messages: 133
Registered: April 2013
Location: INDIA
Senior Member
Hi,
I have below requirement...
CREATE TABLE SCOTT.EMPTEST
(
  ENAME     VARCHAR2(10 BYTE),
  EMPNO     NUMBER(4)                           NOT NULL,
  DEPTNO    NUMBER(2),
  HIREDATE  NUMBER(10)
)

data is
ENAME	EMPNO	DEPTNO	HIREDATE
SMITH	7369	20	19801217
ALLEN	7499	30	19810220
WARD	7521	30	19810222
JONES	7566	20	19810402
MARTIN	7654	30	19810928
BLAKE	7698	30	19810501
CLARK	7782	10	19810609
SCOTT	7788	20	19821209
KING	7839	10	19811117
TURNER	7844	30	19810908
ADAMS	7876	20	19830112
JAMES	7900	30	19811203
FORD	7902	20	19811203
MILLER	7934	10	19820123

I written like this
select to_char(to_date(round(avg(to_number(to_char(to_date(hiredate,'YYYYMMDD'), 'J')))),'J'),'YYYYMMDD')AVG_DATE
,deptno
from emptest 
where hiredate >1
group by deptno

I got the below output
AVG_DATE	DEPTNO

19810630	30
19820108	20
19811017	10


I inserted two more rows
ENAME	EMPNO	DEPTNO	HIREDATE
shankar 4563    40       2000622
TEST1   4564    40       1980213

My required output is
AVG_DATE	DEPTNO
2000622         40
19810630	30
19820108	20
19811017	10

Conditions: I have to get the average hiredate for each deptno which is size less than or equal to 8. In my database date is stored in number format.
Re: sql query to get avg value [message #632019 is a reply to message #632018] Fri, 23 January 2015 07:40 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Quote:
In my database date is stored in number format.


Awesome! Now fix that first. There is no reason why a date needs to be stored as number. It is no more a date, and don't expect any date arithmetic on that poor column. All you do is end up overkilling performance with all that unnecessary functions around the column to convert it to something(date) which should have been done at the first place. Bad design.

Read this https://edstevensdba.wordpress.com/2011/04/07/nls_date_format/

Quote:
where hiredate >1


And what is this condition for?

Edit : typo

[Updated on: Fri, 23 January 2015 07:47]

Report message to a moderator

Re: sql query to get avg value [message #632020 is a reply to message #632019] Fri, 23 January 2015 07:45 Go to previous messageGo to next message
thelearner
Messages: 133
Registered: April 2013
Location: INDIA
Senior Member
Hi Lalit,
thanks you for suggestions...
I don't have rights to change the data....
My client inserted the data like that...
Confused

Please help me on this issue..
Re: sql query to get avg value [message #632021 is a reply to message #632020] Fri, 23 January 2015 07:49 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
>My client inserted the data like that...

You designed it like that not the client.
Re: sql query to get avg value [message #632024 is a reply to message #632018] Fri, 23 January 2015 08:33 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
thelearner wrote on Fri, 23 January 2015 08:31

...to_char(to_date(round(avg(to_number(to_char(to_date(...



Honestly, I have a hard time following when there is so much nesting. If this was on a test, I would probably get it wrong.

[fixed typo]

[Updated on: Fri, 23 January 2015 13:11]

Report message to a moderator

Re: sql query to get avg value [message #632025 is a reply to message #632024] Fri, 23 January 2015 08:46 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
It is the(learner)'s try.
Re: sql query to get avg value [message #632027 is a reply to message #632018] Fri, 23 January 2015 09:01 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
thelearner wrote on Fri, 23 January 2015 08:31
Hi,
I have to get the average hiredate



This makes no sense. What is the logical meaning of average date?

SY.
Re: sql query to get avg value [message #632044 is a reply to message #632018] Fri, 23 January 2015 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In addition to others' posts:

Quote:
I inserted two more rows

ENAME EMPNO DEPTNO HIREDATE
shankar 4563 40 2000622
TEST1 4564 40 1980213


My required output is

AVG_DATE DEPTNO
2000622 40

Why avg_date is not the average?

Re: sql query to get avg value [message #632086 is a reply to message #632044] Sat, 24 January 2015 07:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> I have to get the average hiredate

in order to compute any AVERAGE value, first you must sum two or more initial data points; right.

for example, let us say Joe was hired on 23-JUNE-2007 & Jim was hired on 17-MAY-2010.
PLEASE both show us & describe to us in complete detail, what is the expected & desired results when you SUM these 2 HIREDATE together.

I contend that it is totally nonsensical to suggest that anyone can add multiple dates together & obtain any meaningful result.

Unless & until you can add multiple initial values & obtain a meaningful SUM, it is not possible to compute any AVERAGE!

SQL> select to_date('23-06-2007','DD-MM-YYYY') + TO_DATE('17-05-2010','DD-MM-YYYY') FROM DUAL;
select to_date('23-06-2007','DD-MM-YYYY') + TO_DATE('17-05-2010','DD-MM-YYYY') FROM DUAL
                                          *
ERROR at line 1:
ORA-00975: date + date not allowed

Re: sql query to get avg value [message #632087 is a reply to message #632086] Sat, 24 January 2015 08:31 Go to previous messageGo to next message
John Watson
Messages: 8919
Registered: January 2010
Location: Global Village
Senior Member
One can do this sort of thing:
select to_date('23-06-2007','DD-MM-YYYY'),TO_DATE('17-05-2010','DD-MM-YYYY'),
to_date('23-06-2007','DD-MM-YYYY') + (TO_DATE('17-05-2010','DD-MM-YYYY')-to_date('23-06-2007','DD-MM-YYYY'))/2
from dual;
which seems not unreasonable.
Re: sql query to get avg value [message #632093 is a reply to message #632087] Sat, 24 January 2015 19:22 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
That's not average but rather midpoint - which has some logical meaning, e.g. when was I half of my current age. Date avegage simply has no logical meaning.

SY.

[Updated on: Sat, 24 January 2015 19:23]

Report message to a moderator

Re: sql query to get avg value [message #632107 is a reply to message #632093] Sun, 25 January 2015 04:36 Go to previous message
John Watson
Messages: 8919
Registered: January 2010
Location: Global Village
Senior Member
Perhaps one can get to a true "average". Using 'J' for Julian format one can get numbers that can be used to geneate the mean:
orclz>
orclz> select hiredate,to_char(hiredate,'J') from emp;

HIREDATE            TO_CHAR
------------------- -------
1980-12-17:00:00:00 2444591
1981-02-20:00:00:00 2444656
1981-02-22:00:00:00 2444658
1981-04-02:00:00:00 2444697
1981-09-28:00:00:00 2444876
1981-05-01:00:00:00 2444726
1981-06-09:00:00:00 2444765
1987-04-19:00:00:00 2446905
1981-11-17:00:00:00 2444926
1981-09-08:00:00:00 2444856
1987-05-23:00:00:00 2446939
1981-12-03:00:00:00 2444942
1981-12-03:00:00:00 2444942
1982-01-23:00:00:00 2444993

14 rows selected.

orclz> select to_date(round(sum(to_number(to_char(hiredate,'J')))/count(*)),'J') "mean hiredate" from emp;

mean hiredate
-------------------
1982-05-15:00:00:00

orclz>
which is more-or-less what OP is doing, and if he validates his data entry properly then his problem is solved.
Or for an example of a modal average:
orclz>
orclz> select trunc(hiredate,'yyyy'),count(*) from emp group by trunc(hiredate,'yyyy') order by 2;

TRUNC(HIREDATE,'YYY   COUNT(*)
------------------- ----------
1982-01-01:00:00:00          1
1980-01-01:00:00:00          1
1987-01-01:00:00:00          2
1981-01-01:00:00:00         10

orclz> select modal_hiredate from
  2  (select trunc(hiredate,'yyyy') modal_hiredate,count(*) from emp group by trunc(hiredate,'yyyy') order by 2 desc)
  3  where rownum=1;

MODAL_HIREDATE
-------------------
1981-01-01:00:00:00

orclz>

The median average would be:
orclz>
orclz> select hiredate from emp order by 1;

HIREDATE
-------------------
1980-12-17:00:00:00
1981-02-20:00:00:00
1981-02-22:00:00:00
1981-04-02:00:00:00
1981-05-01:00:00:00
1981-06-09:00:00:00
1981-09-08:00:00:00
1981-09-28:00:00:00
1981-11-17:00:00:00
1981-12-03:00:00:00
1981-12-03:00:00:00
1982-01-23:00:00:00
1987-04-19:00:00:00
1987-05-23:00:00:00

14 rows selected.

orclz>
orclz> select hiredate "median hiredate" from
  2              (select hiredate,rownum rn from
  3                       (select hiredate from emp order by hiredate)
  4             )
  5  where rn = round((select count(*)/2 from emp));

median hiredate
-------------------
1981-09-08:00:00:00

orclz>


Well, that was fun (if a bit slly). I'd better get on with some work now.
Previous Topic: REG:Variable declaration in procedure
Next Topic: Updating a table with group by
Goto Forum:
  


Current Time: Tue Mar 19 06:06:29 CDT 2024