从 SQL Server 中的子查询值或其他聚合函数获取平均值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1394603/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting an average from subquery values or another aggregate function in SQL Server
提问by simon
I have the SQL statement (SQL Server )
我有 SQL 语句（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
页数
27
19
59
Now I would like to get the average of all those figures using SQL. Apparently nested aggregate functions like
现在我想使用 SQL 获得所有这些数字的平均值。显然嵌套的聚合函数，如
(AVG(COUNT(pageCount)))
(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?
如何获得 pageCount 行的平均值？
回答by Robin Day
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
给它一个别名，例如本例中的 MyTable
SELECT
AVG(pageCount)
FROM
(
SELECT
COUNT(ActionName) AS pageCount
FROM
tbl_22_Benchmark
) MyTable
回答by eKek0
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
回答by Mladen Prajdic
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
回答by Charles Bretana
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
回答by Lukasz Lysik
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.
2，你最后没有关闭你的支架。第三，你必须命名你的内部查询。
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
回答by Rudrakshi Marathe
**If you want to calculate average from two different by using procedure **
**如果你想通过使用程序计算两个不同的平均值**
step1:select first column from ut11 table
步骤1：从ut11表中选择第一列
step2: select second column from ut12 table
step2：从ut12表中选择第二列
step3:by using left join join this table
步骤 3：通过使用左连接加入此表
step 4:((t1.ut_1 + t2.ut_2)/2)as total calculate avg
步骤 4:((t1.ut_1 + t2.ut_2)/2) 作为总计算平均值
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
