Home » SQL & PL/SQL » SQL & PL/SQL » Using avg in a sub query
Using avg in a sub query [message #259454] Wed, 15 August 2007 11:53 Go to next message
Reporter
Messages: 10
Registered: August 2007
Junior Member
I'm struggling with the syntax in a sub-query, been looking for examples but can't find what I am trying to do:

The extract from my code below returns the last 3 scores and dates for each id. I now need to calculate the average of the last 3 scores for each id.

SELECT *
 from (SELECT id, date, score, 
 row_number() over (partition by id order by date desc) rn 
 from table1)              
 WHERE rn <= 3


The data returned looks like this:

Quote:
id date score
1 1/1/07 10
1 1/2/07 20
1 1/3/07 30


I need it to look like this:

Quote:
id date score avg
1 1/1/07 10 20
1 1/2/07 20 20
1 1/3/07 30 20


The best I seem to be able to do is this but I can't see how to get the average to calculate at id level:

SELECT *
 from (select avg(score)
 from (SELECT id, date, score, 
 row_number() over (partition by id order by date desc) rn 
 from table1)              
 WHERE rn <= 3)


Re: Using avg in a sub query [message #259461 is a reply to message #259454] Wed, 15 August 2007 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
avg is also an analytic function like row_number.

Regards
Michel
Re: Using avg in a sub query [message #259464 is a reply to message #259461] Wed, 15 August 2007 12:24 Go to previous messageGo to next message
Habeeb
Messages: 57
Registered: August 2000
Member
SQL> select col_id, col_dt, col_score, col_score_avg
  2  from (select col_id, col_dt, col_score,
  3        row_number() over (partition by col_id order by col_score) rn
  4        from table1), 
  5       (select avg(col_score) col_score_avg
  6        from table1
  7        where rownum < 4)
  8  where rn < 4;

    COL_ID COL_DT     COL_SCORE COL_SCORE_AVG
---------- --------- ---------- -------------
         1 12-AUG-07         10            20
         1 13-AUG-07         20            20
         1 14-AUG-07         30            20


Habeeb
Re: Using avg in a sub query [message #259466 is a reply to message #259464] Wed, 15 August 2007 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not what I meant.
You don't have to scan the table twice.
Currently, you don't use avg as an analytic function but as a classical aggregate function.

Regards
Michel
Re: Using avg in a sub query [message #259475 is a reply to message #259466] Wed, 15 August 2007 13:09 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
@Habeeb,
Further addition to Michael's point. This is what Michael meant, i think.
SQL> select * from test;

        ID SOME_DATE      SCORE
---------- --------- ----------
         1 01-JAN-07         10
         1 02-JAN-07         10
         1 03-JAN-07         20
         2 01-JAN-07         10
         2 02-JAN-07         25
         2 04-JAN-07         30
         3 01-JAN-07         40
         2 05-JAN-07         40

SQL> select id, some_date, score, avg(score) over(partition by id) from (
  2  select id, some_date, score, row_number() over(partition by id order by some_date desc) rn from test)
  3  where rn <= 3;

        ID SOME_DATE      SCORE AVG(SCORE)OVER(PARTITIONBYID)
---------- --------- ---------- -----------------------------
         1 03-JAN-07         20                    13.3333333
         1 02-JAN-07         10                    13.3333333
         1 01-JAN-07         10                    13.3333333
         2 05-JAN-07         40                    31.6666667
         2 04-JAN-07         30                    31.6666667
         2 02-JAN-07         25                    31.6666667
         3 01-JAN-07         40                            40


And what you have suggested is
select id, some_date, score, avg_score
from
(select id, some_date, score, row_number() over(partition by id order by some_date desc) rn from
test),
(select avg(score) avg_score from test where rownum < 4)
SQL> /

        ID SOME_DATE      SCORE  AVG_SCORE
---------- --------- ---------- ----------
         1 01-JAN-07         10 13.3333333
         1 02-JAN-07         10 13.3333333
         1 03-JAN-07         20 13.3333333
         2 01-JAN-07         10 13.3333333
         2 02-JAN-07         25 13.3333333
         2 04-JAN-07         30 13.3333333
         3 01-JAN-07         40 13.3333333
         2 05-JAN-07         40 13.3333333 


You can see for yourself the output is completely different because of the following two queries :
I don't think the OP wants this. He want to get the latest three records for each id's (based on the date). This query actually will sort the record but you are not filtering the output.
(select id, some_date, score, row_number() over(partition by id order by some_date desc) rn from test) 

This Query will calculate the average for a random set of three records. Instead OP want to calculate the average for the latest three records.
(select avg(score) avg_score from test where rownum < 4) 


Cheers
Raj

[Updated on: Wed, 15 August 2007 13:10]

Report message to a moderator

Re: Using avg in a sub query [message #259480 is a reply to message #259475] Wed, 15 August 2007 13:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I should like he find the solution by himself there he will learn much more than reading a solution.

Regards
Michel
Re: Using avg in a sub query [message #259484 is a reply to message #259480] Wed, 15 August 2007 13:21 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I agree completely with you, but the reason i posted the query is, to show the posted query is not exactly what the OP has asked.

Point noted.
Re: Using avg in a sub query [message #259492 is a reply to message #259475] Wed, 15 August 2007 13:44 Go to previous messageGo to next message
Habeeb
Messages: 57
Registered: August 2000
Member
Thanks Raj,
avg(score) over(partition by id)

This is something new to me, never used the above function.
Thanks again...

Habeeb
Re: Using avg in a sub query [message #259493 is a reply to message #259492] Wed, 15 August 2007 13:46 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Nice links, which you can have a look at.
Google
By
Vamsi
Re: Using avg in a sub query [message #259497 is a reply to message #259492] Wed, 15 August 2007 14:13 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another one: Database SQL Reference

Regards
Michel
Previous Topic: V11 Documentation advice
Next Topic: DML Error logging
Goto Forum:
  


Current Time: Tue Dec 06 16:11:43 CST 2016

Total time taken to generate the page: 0.12285 seconds