Getting an average from subquery values or another aggregate function in SQL Server

sql, sql-server, t-sql

Solution

I can't see your whole query as it doesn't seem to have posted correctly.

However, I believe your problem is purely a lack of a name for your derived table / nested subquery.

Give it an alias, such as MyTable in this example

SELECT
    AVG(pageCount)
FROM
(
    SELECT 
        COUNT(ActionName) AS pageCount
    FROM
        tbl_22_Benchmark
) MyTable

Problem

I have the SQL statement (SQL Server ) ``` SELECT COUNT(ActionName) AS pageCount FROM tbl_22_Benchmark WHERE DATEPART(dw,CreationDate)>1 AND DATEPART(dw,CreationDate)<7 GROUP BY dateadd(dd,0, datediff(dd,0,CreationDate)) ``` which produces the output pageCount 27 19 59 Now I would like to get the average of all those figures using SQL. Apparently nested aggregate functions like (AVG(COUNT(pageCount))) are not allowed , and using a subquery like ``` SELECT AVG(pageCount) FROM ( SELECT COUNT(ActionName) AS pageCount FROM tbl_22_Benchmark WHERE DATEPART(dw,CreationDate)>1 AND DATEPART(dw,CreationDate)<7 GROUP BY dateadd(dd,0, datediff(dd,0,CreationDate)) ) ``` gets me just an error message Incorrect syntax near ')'. How can I get the average of the pageCount rows?

Original source