Set AVG result AS column

I am running a query in SQL Server 2005. The query needs to have a column that consists of the same number the whole way down. The number is the avg of another row in the query. Here is what I have, maybe it will help make sense of what I am trying. This data has 'color' values and the 'DataExtraLineValue' the same the whole way down because the software that reads this query is spitting out a chart. Basically, why cant I run 开发者_开发问答AVG(TagValueInteger) AS DataExtraLineValue

SELECT
      RecordedDateTime AS DataGroup,
      TagValueInteger AS DataBar,
      TagValueInteger AS DataLine,
      'Green' AS DataColor,
      'Avg' AS DataExtraLineLabel,
       AVG(TagValueInteger) AS DataExtraLineValue,
      'Blue' AS DataExtraLineColor
FROM tTagHistory
WHERE
      (TagHistoryDefinitionID = 2) AND
      (IsQualityGood = 1) AND
      (DeltaValueInteger <> 0) AND
      (TagValue > 4) AND
      (TagValue < 60) AND 
      (RecordedDateTime > (GetDate()-2))
ORDER BY RecordedDateTime DESC

---

You can't do that because there's no grouping. Without grouping, your AVG is going to be the value for each row. If you want the average for ALL values of that field for the whole table, you can make it a subquery:

SELECT ....
       (SELECT AVG(TagValueInteger) FROM tTagHistory) as DataExtraLineValue
       ....

---

Another way to do it is like so:

SELECT 
  ....    
  AVG(TagValueInteger) over () AS DataExtraLineValue
  ....

---

AVG is an aggregate function. Using aggregate functions implies grouping. You either aggregate values in the entire rowset or in groups. In the former case you cannot have non-aggregated columns (except for static values, like your 'Green', 'Avg' ones), and in the latter case you must introduce the GROUP BY clause where you are to list all the columns that define groups: those will go non-aggregated in the SELECT list. Since your query does have some non-aggregated values, it's more likely the second case, and your query should then look somehting like this:

SELECT
      RecordedDateTime AS DataGroup,
      TagValueInteger AS DataBar,
      TagValueInteger AS DataLine,
      'Green' AS DataColor,
      'Avg' AS DataExtraLineLabel,
       AVG(TagValueInteger) AS DataExtraLineValue,
      'Blue' AS DataExtraLineColor
FROM tTagHistory
WHERE
      (TagHistoryDefinitionID = 2) AND
      (IsQualityGood = 1) AND
      (DeltaValueInteger <> 0) AND
      (TagValue > 4) AND
      (TagValue < 60) AND 
      (RecordedDateTime > (GetDate()-2))
GROUP BY RecordedDateTime, TagValueInteger
ORDER BY RecordedDateTime DESC

However, as per your post, it's even more likely that you want to retrieve both aggregated and non-aggregated values. In that case you need windowed aggregation. An aggregated function becomes a windowed aggregated function by adding the OVER clause to it. In your particular case it should probably be like this:

SELECT
      RecordedDateTime AS DataGroup,
      TagValueInteger AS DataBar,
      TagValueInteger AS DataLine,
      'Green' AS DataColor,
      'Avg' AS DataExtraLineLabel,
       AVG(TagValueInteger) OVER () AS DataExtraLineValue,
      'Blue' AS DataExtraLineColor
FROM tTagHistory
WHERE
      (TagHistoryDefinitionID = 2) AND
      (IsQualityGood = 1) AND
      (DeltaValueInteger <> 0) AND
      (TagValue > 4) AND
      (TagValue < 60) AND 
      (RecordedDateTime > (GetDate()-2))
ORDER BY RecordedDateTime DESC

AVG(TagValueInteger) OVER () returns the average value of TagValueInteger per the entire rowset. Alternatively you can modify the OVER () clause so as to return different (possibly repeating) values per different groups, for example `AVG(TagValueInteger) OVER (PARTITION BY DataGroup) will return different average values each for different value of DataGroup. You can read more about it in the linked article.