How can I get an average from a query dataset in SQL Server?

Question

I am building a parts inventory system, and have a transaction table that stores how often an item in used.
Let's say Item A is used 10 times in 2021, 20 in 2022, and 30 in 2023. Of course each transaction is on a different day throughout each year. So, by using

SELECT ItemNumber, SUM(QuantityUsed), YEAR(DateUsed)
FROM ItemTransactions
GROUP BY ItemNumber, YEAR(DateUsed)
WHERE ItemNumber = 1001

I can get how many were used each year. My question is how can I get the average yearly use from this dataset? I've been attempting to use a subquery, but have not been able to get it working right.

Thanks!

Answer 1

There are several ways to skin the cat here.

1. Derived table

Use your query as a table and apply additional grouping and aggregation to its results:

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

(Also suggested in the other answer.)

This is basically the same as the derived table option, only nesting is expressed using different syntax:

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
;

One slight correction to the original query that I have made in the two options above is that I have left out YEAR(DateUsed) from the SELECT clause as unnecessary for average calculation. (It does need to stay in the inner GROUP BY, though.)

Here are a couple more that do not involve nesting.

3. Window aggregation + DISTINCT

You can apply the window version of AVG directly to the SUM expression. The result will be the sums replaced with identical averages per ItemNumber, and you will get a row set containing multiple duplicates. You can suppress the duplicates by just replacing the SELECT with 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. SUM() / COUNT(DISTINCT)

You can also remind yourself that the yearly average of QuantityUsed per ItemNumber is essentially the total QuantityUsed per ItemNumber divided by the total number of distinct years per ItemNumber, and rewrite the query accordingly:

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

Answer 2

you can use a CTE for that, the group by of the outer select is useful if you need information in more than one 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

Answer 3

All answers posted here so far assume that the years in which the item was not used should not contribute to the average.

In other words, if the yearly usage is 10, 0, 10, the answer you will get will be 10 (not 6.66).

If you have items that are listed in the inventory somewhere, but not actually being used in transactions, you will not get any answer for them (instead of getting the item id with a zero next to it).

This all might be actually what you want, or it might not be a problem for your system because most items are actually being used in every year since they've been introduced, and you don't care about occasional outliers. In this case, use any of the answers above.

If you want all the years and all the items to contribute to the average, even those that didn't have any transactions, use one of these queries below.

Fixed years

This query calculates the average of yearly consumption of every item in your system during a fixed time span. Since the system doesn't know what time span you want to calculate the average over (maybe you want the average yearly consumption of iPhones since 1900), you'll need to provide it to the query explicitly.

Note that when calculating yearly averages, you might want to omit records for the current year, as it still hasn't finished and is likely to skew your data.

SELECT  i.Id, SUM(COALESCE(QuantityUsed, 0)) * 1. / DATEDIFF(year, '2014-01-01', GETDATE()) -- 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

This will also give you the correct answer of zero for the items that have never been consumed in a transaction at all, instead of omitting them from the resultset.

First transaction as start date

You might want to treat the first transaction that happened with the item as the date of its introduction. In this case, use this:

SELECT  i.Id, COALESCE(SUM(QuantityUsed) * 1. / DATEDIFF(year, MIN(DateUsed), GETDATE()), 0)
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

Actual dates

Your system might also be storing the actual dates of introduction and, possibly, discontinuation of each item. In this case, just put these dates into the DATEDIFF function in the SELECT clause.