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

Question

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?

Answer 1

in your second attempt you're missing a ) and an alias:

SELECT AVG(pageCount) as AvgPageCount FROM
(
    SELECT 
    COUNT(ActionName) AS pageCount
    FROM tbl_22_Benchmark
    WHERE DATEPART(dw,CreationDate)>1 AND DATEPART(dw,CreationDate)
) t

Answer 2

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

Answer 3

Add a subquery alias

SELECT AVG(pageCount) 
FROM (SELECT COUNT(ActionName) AS pageCount
      FROM tbl_22_Benchmark
      WHERE DATEPART(dw,CreationDate)>1 
         AND DATEPART(dw,CreationDate) {Missing stuff here } ) AS Z

Answer 4

First of all you shoud add condition on the end of query. For example:

WHERE DATEPART(dw,CreationDate)>1 AND DATEPART(dw,CreationDate) < 10

2nd, you didn't close your bracket at the end. 3rd, you have to name your inner query.

This should work

SELECT AVG(pageCount) FROM
( 
    SELECT 
    COUNT(ActionName) AS pageCount
    FROM tbl_22_Benchmark
    WHERE DATEPART(dw,CreationDate)>1 AND DATEPART(dw,CreationDate) < 10
) myInnerTable

Answer 5

Your subquery should have an alias, like in this

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))
) AS t