CODEWITHSUNDEEP
sqlsql-server
Ben
Ben

Reputation: 609

Get aggregate value from inner query

This works fine:

SELECT TOP 100 ExecutionTime 
FROM [Brokermate_CONFIG].[dbo].[QueryLog] 
ORDER BY ExecutedOn DESC

But trying to get the average gives me an error

SELECT AVG(executiontime) 
FROM
    (SELECT TOP 100 ExecutionTime 
     FROM [Brokermate_CONFIG].[dbo].[QueryLog] 
     ORDER BY ExecutedOn DESC
    )

Error:

Incorrect syntax near ')'.

What's the correct syntax?

Thanks

Upvotes: 0

Views: 33

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

Add alias to your subquery:

SELECT AVG(sub.executiontime) AS average_execution_time
FROM (SELECT TOP 100 ExecutionTime 
      FROM [Brokermate_CONFIG].[dbo].[QueryLog] 
      ORDER BY ExecutedOn DESC) AS sub

Upvotes: 3

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

You've missed alias name for Sub Query

Try Below

SELECT AVG(executiontime) 
FROM
 (SELECT TOP 100 ExecutionTime 
  FROM [Brokermate_CONFIG].[dbo].[QueryLog]   
  ORDER BY ExecutedOn DESC
 ) A -- You need to have alias name here

Upvotes: 3

Related Questions