CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user4441082
user4441082

Reputation: 371

Calculating average by using where or having clause

I have a table T1 looks like:

acctid  time transactid  amnt
 233    xxx    2         400

I want to return all the transaction with amnt > the average(amnt) in the table. Here is my code:

select * from (select transactid,amnt,avg(amnt) 
over (partition by transactid) avgamnt from T1) where amnt>avg(mnt)
over (partition by transactid)

However, I am thinking another one using having clause

select * from (select transactid,amnt,avg(amnt) 
    over (partition by transactid) avgamnt from T1) having amnt>avg(mnt)
    over (partition by transactid)

May I ask which one is better(or correct) and why please?

Upvotes: 0

Views: 63

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Your query is almost there:

select *
from (select transactid, amnt,
             avg(amnt) over (partition by transactid) as avgamnt 
      from T1
     ) t
where amnt > avgamnt;

You define the column in the subquery. Then, you just have to use it.

Upvotes: 1

void
void

Reputation: 7890

I want to return all the transaction with amnt > the average(amnt) in the table.

so why not use this:

select * from T1 where T1.amnt > (select avg(t.amnt) from T1 t)

Upvotes: 0

Related Questions