Como posso obter uma média de um conjunto de dados de consulta no SQL Server?

Existem várias maneiras de esfolar o gato aqui.

1. Tabela derivada

Use sua consulta como uma tabela e aplique agrupamento e agregação adicionais aos resultados:

SELECT
  ItemNumber, AVG(QuantityUsed) AS AvgQuantityUsed
FROM
  (
    SELECT
      ItemNumber, SUM(QuantityUsed) AS QuantityUsed
    FROM
      dbo.ItemTransactions
    WHERE
      ItemNumber = 1001
    GROUP BY
      ItemNumber, YEAR(DateUsed)
  ) AS derived
GROUP BY
  ItemNumber
;

2. CTE

(Também sugerido na outra resposta .)

Isto é basicamente o mesmo que a opção de tabela derivada, apenas o aninhamento é expresso usando uma sintaxe diferente:

WITH
  derived AS
  (
    SELECT
      ItemNumber, SUM(QuantityUsed) AS QuantityUsed
    FROM
      dbo.ItemTransactions
    WHERE
      ItemNumber = 1001
    GROUP BY
      ItemNumber, YEAR(DateUsed)
  )
SELECT
  ItemNumber, AVG(QuantityUsed) AS AvgQuantityUsed
FROM
  derived
GROUP BY
  ItemNumber
;

Uma ligeira correção na consulta original que fiz nas duas opções acima é que deixei de fora YEAR(DateUsed)a cláusula SELECT por ser desnecessária para o cálculo da média. (No entanto, ele precisa permanecer no GROUP BY interno.)

Aqui estão mais alguns que não envolvem aninhamento.

3. Agregação de janelas + DISTINCT

Você pode aplicar a versão da janela AVGdiretamente à SUMexpressão. O resultado serão as somas substituídas por médias idênticas por ItemNumbere você obterá um conjunto de linhas contendo várias duplicatas. Você pode suprimir as duplicatas apenas substituindo SELECT por SELECT DISTINCT:

SELECT DISTINCT
  ItemNumber, AVG(SUM(QuantityUsed)) OVER (PARTITION BY ItemNumber) AS AvgQuantityUsed
FROM
  dbo.ItemTransactions
WHERE
  ItemNumber = 1001
GROUP BY
  ItemNumber, YEAR(DateUsed)

4. SOMA() / CONTAGEM(DISTINTO)

Você também pode lembrar que a média anual de QuantityUsedper ItemNumberé essencialmente o total QuantityUsedper ItemNumberdividido pelo número total de anos distintos per ItemNumbere reescrever a consulta de acordo:

SELECT
  ItemNumber, SUM(QuantityUsed) / COUNT(DISTINCT YEAR(DateUsed)) AS AvgQuantityUsed
FROM
  dbo.ItemTransactions
WHERE
  ItemNumber = 1001
GROUP BY
  ItemNumber
;

você pode usar um CTE para isso, o agrupamento por da seleção externa é útil se você precisar de informações em mais de um item

WITH CTE as (
SELECT ItemNumber, SUM(QuantityUsed) as QuantityUsed, YEAR(DateUsed)
FROM ItemTransactions
WHERE ItemNumber = 1001
GROUP BY ItemNumber, YEAR(DateUsed)
)
SELECT ItemNumber, AVG(QuantityUsed) AS AVGQuantityUsed
FROM CTE
GROUP BY ItemNumber

Todas as respostas postadas aqui até agora assumem que os anos em que o item não foi utilizado não devem contribuir para a média.

Em outras palavras, se o uso anual for 10, 0, 10, a resposta que você obterá será 10 (não 6,66).

Se você tiver itens listados no inventário em algum lugar, mas que não estão sendo usados ​​em transações, você não receberá nenhuma resposta para eles (em vez de obter o ID do item com um zero próximo a ele).

Tudo isso pode ser realmente o que você deseja ou pode não ser um problema para o seu sistema, porque a maioria dos itens está sendo usada todos os anos desde que foram introduzidos e você não se importa com valores discrepantes ocasionais. Nesse caso, use qualquer uma das respostas acima.

Se você deseja que todos os anos e todos os itens contribuam para a média, mesmo aqueles que não tiveram nenhuma movimentação, utilize uma das consultas abaixo.

Anos fixos

Esta consulta calcula a média do consumo anual de cada item do seu sistema durante um intervalo de tempo fixo. Como o sistema não sabe em que intervalo de tempo você deseja calcular a média (talvez você queira o consumo médio anual de iPhones desde 1900), será necessário fornecê-lo explicitamente à consulta.

Observe que, ao calcular as médias anuais, talvez você queira omitir os registros do ano atual, pois ele ainda não terminou e provavelmente distorcerá seus dados.

SELECT  i.Id, SUM(COALESCE(QuantityUsed, 0)) * 1. / (DATEDIFF(year, '2014-01-01', GETDATE()) + 1) -- start of the time span you're interested in.
FROM    Items i -- list of all the items in the system
LEFT JOIN
        ItemTransactions it
ON      it.ItemNumber = i.Id
        AND it.DateUsed < DATETRUNC(year, GETDATE())
        AND it.DateUsed >= '2014-01-01'
GROUP BY
        i.Id

Isso também lhe dará a resposta correta de zero para os itens que nunca foram consumidos em uma transação, em vez de omiti-los do conjunto de resultados.

Primeira transação como data de início

Você pode querer tratar a primeira transação que aconteceu com o item como a data de sua introdução. Neste caso, use isto:

SELECT  i.Id, COALESCE(SUM(QuantityUsed) * 1. / (DATEDIFF(year, MIN(DateUsed), GETDATE()), 0) + 1)
FROM    Items i -- list of all the items in the system
LEFT JOIN
        ItemTransactions it
ON      it.ItemNumber = i.Id
        AND it.DateUsed < DATETRUNC(year, GETDATE())
GROUP BY
        i.Id

Datas reais

Seu sistema também pode estar armazenando as datas reais de introdução e, possivelmente, de descontinuação de cada item. Neste caso, basta colocar essas datas na DATEDIFFfunção da SELECTcláusula.