CODEWITHSUNDEEP
sqloracle-database
George Palasanu
George Palasanu

Reputation: 3

SELECT avg FROM SELECT avg

I just dont kow how to make it work, how can i select the avg from the other select 

select avg(avg(valoare)) from (select avg(valoare) from note 
where nr_matricol=111 group by ID_CURS)

I've Also tried 

  select avg(alias) from (select avg(valoare) as "alias" from note 
  where nr_matricol=111 group by ID_CURS)

Upvotes: 0

Views: 963

Answers (2)

Mayur Sawant
Mayur Sawant

Reputation: 11

You can use the below query.

select avg(alias) from 
      (select avg(valoare) as alias from note 
         where nr_matricol=111 group by ID_CURS) X

OR

select avg(avg(valoare)) as alias from note 
    where nr_matricol=111 group by ID_CURS X

Upvotes: 1

user330315
user330315

Reputation:

The second query is basically what you want. However once you start using double quotes for identifiers in SQL, these identifiers become case-sensitive: "alias" is a different name then alias (because alias is the same as ALIAS).

So you need to use the double quotes throughout the query:

select avg("alias") 
from (
  select avg(valoare) as "alias" 
  from note 
  where nr_matricol=111 group by ID_CURS
)

Another option is to use a name that does not require quoting:

select avg(avg_valoare) 
from (
  select avg(valoare) as avg_valoare
  from note 
  where nr_matricol=111 group by ID_CURS
)

Although not required by Oracle it is good coding style to give the derived table an alias as well. 

select avg("alias") 
from (
  select avg(valoare) as "alias" 
  from note 
  where nr_matricol=111 group by ID_CURS
) x --<<< here

Note that Oracle does not support the AS keyword for a table alias, so you can't use ) as x for the derived table alias.

Upvotes: 1

Related Questions