find average of not null records

raghvendrapanda

Hy, Is there any function through which i can find the average of the not null rows only.Like total/number of rows containg data Thankz & Ragards , raghvendra Panda

R Giskard Reventlov

Why would you store rows that have no data? In any case maybe what you are asking is how, for instance, do I get all records where one field is not null?

select count(*) from table where not column3 is null

might be close to what you want.

me, me, me "The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!" Larry Niven

thatraja

I hope it will help you. NORTHWIND Database

SELECT
COUNT(UnitPrice) AS [NoOfItems], SUM(UnitPrice) AS [TOTAL], AVG(UnitPrice) AS [AVERAGE]
FROM Products
WHERE UnitPrice IS NOT NULL

OUTPUT

NoOfItems TOTAL AVERAGE

77 2222.71 28.8663

thatraja

Tips/Tricks|Brainbench certifications

raghvendrapanda

Thnks for ur reply I had taken avg(isnull(column),0)..is that the reason why my function is also considering null rows also?

Chris Meech

raghvendrapanda wrote:

avg(isnull(column),0)..

The answer is yes, but as a test why don't you try the following.

avg(isnull(column),42) ..

:)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

Luc Pattyn

I see, the "average" function needs to be calibrated first. :laugh:

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

Getting an article published on CodeProject should be easier and faster.

Chris Meech

Just like the random function needs to be seeded. :-D

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

PIEBALDconsult

I'm not sure that the WHERE UnitPrice IS NOT NULL is required here -- COUNT(UnitPrice) will only count the non-null entries anyway, right? Same with SUM and AVG?