Solved

Getting the average from a Sum/Count query

Posted on 2008-06-14
3
327 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 500 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

735 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