sql query to get avg value [message #632018] |
Fri, 23 January 2015 07:31 |
|
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 |
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 #632024 is a reply to message #632018] |
Fri, 23 January 2015 08:33 |
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 #632087 is a reply to message #632086] |
Sat, 24 January 2015 08:31 |
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 #632107 is a reply to message #632093] |
Sun, 25 January 2015 04:36 |
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.
|
|
|