CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Pradap Pandian
Pradap Pandian

Reputation: 406

How to find Max from Avg in SQL Server 2008

select
    max(total_price_new) 
from
    (select
         avg(Total_Price) as total_price_new, 
         Document_Ref  
     from
         TBL_Sales
     group by
         Document_Ref)

This works in SQLite but doesn't work in SQL Server 2008.

I get this error:

enter image description here

Can anyone know the alternate solution for this?

Upvotes: 1

Views: 84

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You don't need a subquery for this:

select top (1) avg(Total_Price) as total_price_new
from TBL_Sales
group by Document_Ref
order by total_price_new desc;

A nice advantage of this approach is that you can also pull back a Document_Ref with the highest average.

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

You need an alias(q) for your inner query :

select max(q.total_price_new) 
  from  ( select avg(Total_Price) as total_price_new, Document_Ref 
            from TBL_Sales
           group by Document_Ref) as q;

This reference may be helpful.

Upvotes: 3

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

When you write any inner sub-query then you have to put that sub-query name for selecting data into outer query from that sub-query result, so you just need a alias of your sub-query 

select
    max(total_price_new) 
from
    (select
         avg(Total_Price) as total_price_new, 
         Document_Ref  
     from
         TBL_Sales
     group by
         Document_Ref) as T

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

You need table name alias eg Tname

select max(total_price_new) 
from  ( 
  select avg(Total_Price) as total_price_new, Document_Ref 
  from  TBL_Sales
group by Document_Ref
)  Tname

Upvotes: 4

Related Questions