如何在AVG函数中使用别名?

I have the following code below and I'm trying to get an AVG for the 3 columns.

我有下面的代码，我正在尝试为这3列得到一个AVG。

 SELECT 
    (SUM(score) * .3) As score_a,
    (SUM(score) * .6) As score_b,
    (SUM(score) * .8) As score_c

     --now I want to get the AVG of the above scores
    AVG(score_a + score_b + score_c) As avg_score
 FROM score_table

but this does not work. The error I'm getting is "Invalid column name score_a". I'm using SQL Server 2008

但这行不通。我得到的错误是“无效列名score_a”。我正在使用SQL Server 2008

3 个解决方案

#1

AVG() is an aggregate function that takes values from multiple rows and gives their average.

AVG()是一个聚合函数，它从多个行获取值并给出它们的平均值。

You're trying to average 3 columns.

求3列的平均值。

I your specific case, it can be done using algebra.

你的具体情况，可以用代数来做。

SELECT 
  (SUM(score) * .3)                       As score_a,
  (SUM(score) * .6)                       As score_b,
  (SUM(score) * .8)                       As score_c,
  (SUM(score) * ((0.3 + 0.6 + .8) / 3.0)) As score_avg
FROM
  score_table

In a more generalised case, you're limited by the fact that you can't reference a column you just defined in another column...

在更一般化的情况下，由于无法引用您在另一个列中定义的列，您就受到了限制。

SELECT
  a + 1      AS inc_a,
  inc_a * 2  AS this_is_invalid
FROM
  your_table

You'd either need to repeat yourself, or use a sub query...

您需要重复自己的操作，或者使用子查询……

Repetition

重复

SELECT 
  (SUM(score) * .3)                                                 As score_a,
  (SUM(score) * .6)                                                 As score_b,
  (SUM(score) * .8)                                                 As score_c,
  ((SUM(score) * .3) + (SUM(score) * .3) + (SUM(score) * .3)) / 3.0 As score_avg
FROM
  score_table

Sub Query

子查询

SELECT
  score_a,
  score_b,
  score_c,
  (score_a + score_b + score_c) / 3.0 AS score_avg
FROM
(
  SELECT 
    (SUM(score) * .3)                                               As score_a,
    (SUM(score) * .6)                                               As score_b,
    (SUM(score) * .8)                                               As score_c
  FROM
    score_table
)
  AS data

#2

Could you try:

你可以尝试:

 SELECT 
    (SUM(score) * .3) As score_a,
    (SUM(score) * .6) As score_b,
    (SUM(score) * .8) As score_c,
    AVG(score_a + score_b + score_c) As avg_score
 FROM score_table

and see if that works for you?

看看对你是否有效?

#3

There's the brute-force method of doing the average manually, but it won't scale as well as you add columns. :)

有一种手动进行平均的蛮力方法，但是它不会像添加列那样容易伸缩。:)

SELECT CAST((score_a + score_b + score_c) / 3 AS DECIMAL(20,2)) As avg_score,
       score_a,
       score_b,
       score_c
FROM (
    SELECT (SUM(score) * .3) As score_a,
           (SUM(score) * .6) As score_b,
           (SUM(score) * .8) As score_c
    FROM score_table
    GROUP BY userid, gameid --Whatever you're grouping by here!
)

#1