Home » SQL & PL/SQL » SQL & PL/SQL » AVERAGE (ORACLE 10 G)
AVERAGE [message #341511] Tue, 19 August 2008 02:49 Go to next message
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 #341513 is a reply to message #341511] Tue, 19 August 2008 03:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide:
1/ Don't post in UPPER case
2/ Format your post
3/ Post a test case
4/ Post what you already tried
5/ Post your Oracle version (4 decimals)

Regards
Michel
Re: AVERAGE [message #341529 is a reply to message #341513] Tue, 19 August 2008 04:04 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
icon14.gif  Re: AVERAGE [message #341694 is a reply to message #341529] Wed, 20 August 2008 00:24 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #341792 is a reply to message #341773] Wed, 20 August 2008 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 19 August 2008 10:06
Please read OraFAQ Forum Guide:
1/ Don't post in UPPER case
2/ Format your post
3/ Post a test case
4/ Post what you already tried
5/ Post your Oracle version (4 decimals)

Regards
Michel


Re: AVERAGE [message #341809 is a reply to message #341783] Wed, 20 August 2008 06:52 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Quote:

Only 3 FAST 10+15+10/3, but you want 10+15+15+10/4 (Including SLOW) what is the logic?? please explain


 select a, avg(d) from test_00 GROUP BY a



Regards,
Oli

[Updated on: Wed, 20 August 2008 06:54]

Report message to a moderator

Re: AVERAGE [message #341859 is a reply to message #341773] Wed, 20 August 2008 10:44 Go to previous message
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
Previous Topic: How to throw out a "set' of data?
Next Topic: invalid identifier error
Goto Forum:
  


Current Time: Mon Feb 10 11:54:41 CST 2025