?
Solved

Getting the average from a Sum/Count query

Posted on 2008-06-14
3
Medium Priority
?
339 Views
Last Modified: 2008-06-14
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
Comment
Question by:georgep7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 2

Expert Comment

by:osiara
ID: 21784886

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
 
LVL 2

Accepted Solution

by:
osiara earned 2000 total points
ID: 21784906
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
 

Author Comment

by:georgep7
ID: 21784918
Thanks for helping out again osiara!
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question