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

user_76777 0
Viewed 65925

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?

tsql sql-server sql
6 Answers

**If you want to calculate average from two different by using procedure **

step1:select first column from ut11 table

step2: select second column from ut12 table

step3:by using left join join this table

step 4:((t1.ut_1 + t2.ut_2)/2)as total calculate avg

SELECT    t1.ut_1
         ,t2.ut_2
         ,((t1.ut_1 + t2.ut_2)/2) AS total
FROM      ut11 AS t1 
LEFT JOIN ut12 AS t2 ON t1.roll_no = t2.roll_no 
WHERE     t1.roll_no= rno
user_104630230
Related