AVERAGE [message #341511] |
Tue, 19 August 2008 02:49  |
mudalimuthu
Messages: 64 Registered: May 2005 Location: Bangalore
|
Member |
|
|
I HAVE A TABLE LIKE FOLLOWING :
DESCRIPTION SAMPLENO MANUAL VALUE
A 1 FAST 10
A 2 FAST 15
A 3 SLOW 15
A 4 FAST 10
I WANT THE AVERAGE FOR THIS TABLE RESULT AS FOLLOWS
DESCRIPTION MANUAL VALUE
A FAST 12.5
BECAUSE I HAVE THREE ROWS OF FAST IN MANUAL COLUMN SO I WANT TO RETRIEVED FAST AS MY RESULT SET.
ANY BODY CAN HELP ME OUT
MUTHU
|
|
|
|
Re: AVERAGE [message #341529 is a reply to message #341513] |
Tue, 19 August 2008 04:04   |
mudalimuthu
Messages: 64 Registered: May 2005 Location: Bangalore
|
Member |
|
|
oracle database 10g express edition release 10.2.0.1.0 - beta
i have a table like following :
table :test1
description sampleno manual value
a 1 fast 10
a 2 fast 15
a 3 slow 15
a 4 fast 10
i want the average for this table result as follows
description manual value
a fast 12.5
because i have three rows of fast in manual column so i want to retrieved fast as my result set.
I have tried the following sql
select description, max(manual) manual, avg(value) value from test1
group by description
the output for manual is not correct in my sql. is there any way to find out average for manual column....
the value which contains maximum no. of row should be retrieved..
|
|
|
Re: AVERAGE [message #341538 is a reply to message #341511] |
Tue, 19 August 2008 04:31   |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
Please see the previous post to format the post, post test case etc.,...
Anyway try this for your requirenment.
SELECT a,c, AVG(d)
FROM test3
GROUP BY a,c
|
|
|
Re: AVERAGE [message #341694 is a reply to message #341529] |
Wed, 20 August 2008 00:24   |
|
Try the following query:
select * from (select description,manual, avg(value) value from test1
group by manual,description order by count(manual) desc) where rownum =1;
|
|
|
Re: AVERAGE [message #341773 is a reply to message #341694] |
Wed, 20 August 2008 04:45   |
mudalimuthu
Messages: 64 Registered: May 2005 Location: Bangalore
|
Member |
|
|
select description, max(manual) mnaual, round(avg(value), 2) value
from
(
select description, manual, round(avg(value),2) value from test1 a
group by description, manual
having count(manual) = (select max(count(manual)) from test1 b
where a.description = b.description group by manual)
)
group by description
final i got the output by the above sql. but is there any simple way to get same output
|
|
|
Re: AVERAGE [message #341783 is a reply to message #341511] |
Wed, 20 August 2008 05:37   |
spmano1983
Messages: 269 Registered: September 2007
|
Senior Member |
|
|
DESCRIPTION SAMPLENO MANUAL VALUE
A 1 FAST 10
A 2 FAST 15
A 3 SLOW 15
A 4 FAST 10
Hi,
Only 3 FAST 10+15+10/3, but you want 10+15+15+10/4 (Including SLOW) what is the logic?? please explain
|
|
|
|
|
Re: AVERAGE [message #341859 is a reply to message #341773] |
Wed, 20 August 2008 10:44  |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
I think this is what you are after. Try to build your query on top of this.
SQL> with t
as
(select 'A' description, 1 sample_no, 'FAST' manual, 10 value from dual union all
select 'A', 2, 'FAST', 15 from dual union all
select 'A', 3, 'SLOW', 15 from dual union all
select 'A', 4, 'FAST', 10 from dual
)
select * from t;
DESCRIPTION SAMPLE_NO MANUAL VALUE
A 1 FAST 10
A 2 FAST 15
A 3 SLOW 15
A 4 FAST 10
SQL> with t
as
(select 'A' description, 1 sample_no, 'FAST' manual, 10 value from dual union all
select 'A', 2, 'FAST', 15 from dual union all
select 'A', 3, 'SLOW', 15 from dual union all
select 'A', 4, 'FAST', 10 from dual
)
select description, avg(value) over(partition by description) avg_val,
count(description) over(partition by description, manual) total_count from t;
DESCRIPTION AVG_VAL TOTAL_COUNT
A 12.5 3
A 12.5 3
A 12.5 3
A 12.5 1
Please bear with my formatting as I have not used sql*plus and I have used apex.
Regards
Raj
|
|
|