Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Getting the average from a Sum/Count query

Posted on 2008-06-14
3
Medium Priority
?
346 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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 response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

618 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