• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

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.
0
georgep7
Asked:
georgep7
  • 2
1 Solution
 
osiaraCommented:

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

Open in new window

0
 
osiaraCommented:
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

Open in new window

0
 
georgep7Author Commented:
Thanks for helping out again osiara!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now