Menu
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?
702
AVG() function is an aggregate function that calculates the average value of a numerical dataset that returns from the SELECT statement.
It turned out that subqueries are not allowed in aggregate functions.
This query throws an error, because you cannot use AVG() in a WHERE condition. Since AVG() is a group function, you can only use it in a SELECT statement or in a HAVING clause. The query inside a main query is called a subquery.
Advantage of Using CTE Instead of having to declare the same subquery in every place you need to use it, you can use CTE to define a temporary table once, then refer to it whenever you need it. CTE can be more readable: Another advantage of CTE is CTE are more readable than Subqueries.
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 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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
