CODEWITHSUNDEEP
sqlsql-servert-sql
simon
simon

Reputation: 12902

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

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?

Upvotes: 33

Views: 77150

Answers (6)

Rudrakshi Marathe
Rudrakshi Marathe

Reputation: 11

**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

Upvotes: 1

eKek0
eKek0

Reputation: 23289

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

Upvotes: 8

Lukasz Lysik
Lukasz Lysik

Reputation: 10610

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

Upvotes: 2

Charles Bretana
Charles Bretana

Reputation: 146449

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

Upvotes: 4

Robin Day
Robin Day

Reputation: 102468

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

Upvotes: 53

Mladen Prajdic
Mladen Prajdic

Reputation: 15677

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

Upvotes: 3

Related Questions