# Getting the average from a Sum/Count query

I have the following query:

SELECT ItemSerialNumber, SUM(CASE WHEN YEAR(SoldDate) = 2008 THEN SoldPrice ELSE 0 END) AS SumOfPrices, SUM(CASE WHEN YEAR(SoldDate) = 2008 THEN 1 ELSE 0 END) AS TotalCount,
FROM tProd
GROUP BY ItemSerialNumber

how can I adjust this to get the average ( the SumOfPrices divided by the TotalCount)? Thanks.
###### Who is Participating?

Commented:
or
``````SELECT
ItemSerialNumber,
SUM(CASE WHEN YEAR(SoldDate) = 2008 THEN SoldPrice ELSE 0 END) AS SumOfPrices,
SUM(CASE WHEN YEAR(SoldDate) = 2008 THEN 1 ELSE 0 END) AS TotalCount,
CASE WHEN SUM(CASE WHEN YEAR(SoldDate) = 2008 THEN 1 ELSE 0 END) = 0 THEN NULL ELSE SUM(CASE WHEN YEAR(SoldDate) = 2008 THEN SoldPrice ELSE 0 END) / SUM(CASE WHEN YEAR(SoldDate) = 2008 THEN 1 ELSE 0 END) END AS AvgPricesTotal
FROM tProd
GROUP BY ItemSerialNumber
``````
0

Commented:

``````SELECT ItemSerialNumber, SumOfPrices, TotalCount, CASE WHEN TotalCount = 0 THEN NULL ELSE SumOfPrices / TotalCount END AS AvgPrices
FROM (
SELECT ItemSerialNumber, SUM(CASE WHEN YEAR(SoldDate) = 2008 THEN SoldPrice ELSE 0 END) AS SumOfPrices, SUM(CASE WHEN YEAR(SoldDate) = 2008 THEN 1 ELSE 0 END) AS TotalCount,
FROM tProd
GROUP BY ItemSerialNumber
) AS pom
``````
0

Author Commented:
Thanks for helping out again osiara!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.