Obtain average no-NULL fields in query

Question

I'm trying to obtain the average of not null fields in a query. Right now I'm making:

select COUNT(name) as name
,COUNT(surname) as surname  
    ,COUNT(email) as email from table where id = 1

And then I manually sum all and then calculate the average making a division with the total fields. I'm sure there is a more automatic ad elegant way of doing that but I'm not seeing it

Thanks!

EDIT EXAMPLE

query result:

Name       Surname     Email
-----------------------------
John       Lennon      NULL
Mary       NULL        NULL
Peter      Clock       222@aa.com

total fields: 9 no-null fields: 6

average no null fields: 6/9 = 0,66 -> 66% Thats what I want

sorry for not being clear

For me, it's not clear what you want to achieve. Please provide sample data and the expected output. — Daniel Hilgarth, Jun 09 '11 at 13:01
You mean count non-null values in all rows for all 3 columns? Not per column? Or count/average of rows *with* null values? — gbn, Jun 09 '11 at 13:05

score 1 · Answer 1 · answered Jun 09 '11 at 13:11

1

It's unclear what you want, but just guessing, try:

select (COUNT(name) + COUNT(surname) + COUNT(email)) / 3 from table;

answered Jun 09 '11 at 13:11

Bohemian

412,405
93
575
722

Sum the count() is ok but I have to divide to the total of fields, 9 in the example – Juan Jun 09 '11 at 13:31

score 1 · Answer 2 · 2011-06-09T13:45:56.327

I think this is the very unefficient way, but it's easy to understand, so beat me up :)

DECLARE @TotalCount NUMERIC(5, 2)
DECLARE @NullNameCount NUMERIC(5, 2)
DECLARE @NullSurnameCount NUMERIC(5, 2)
DECLARE @NullEmailCount NUMERIC(5, 2)

SELECT @TotalCount = COUNT(0) FROM Table

SELECT @NullNameCount = COUNT(0) FROM Table WHERE Name IS NOT NULL
SELECT @NullSurnameCount = COUNT(0) FROM Table WHERE Surname IS NOT NULL
SELECT @NullEmailCount = COUNT(0) FROM Table WHERE Email IS NOT NULL

SELECT CAST((((@NullNameCount + @NullSurnameCount + @NullEmailCount) / @TotalCount) * 100.00) AS NUMERIC(5, 2))

gbn · Accepted Answer · 2011-06-09T13:47:04.143

1

COUNT(*) gives simply row count
3 * COUNT(*) gives number of elements in the table
COUNT(colname) gives non-null values in that column
COUNT(colname1) + COUNT(colname2) + COUNT(colname3) gives all non-null values

The rest is basic arithmatic with some implicit decimal/float conversions to remove integer division issues

select
   100.0 * 
   (
      COUNT(name) + COUNT(surname) + COUNT(email)
   )
   /
   (
       3 * COUNT(*)
   )
from
   table where id = 1

edited Jun 09 '11 at 13:47

answered Jun 09 '11 at 13:43

gbn

422,506
82
585
676

But what about NOT NULL condition ? I think I don't get it. – Jun 09 '11 at 13:47
2

@daemon_x: `COUNT(column)` *ignores* null values – gbn Jun 09 '11 at 13:48
wow, I haven't noticed that ever. I used `COUNT` only with constant value so I can't see that before. Thanks for the explanation. +1 anyway – Jun 09 '11 at 13:54
1

@daemon_x: here's some more... http://stackoverflow.com/questions/1221559/count-vs-count1/1221649#1221649 – gbn Jun 09 '11 at 13:56

score 0 · Answer 4 · answered Jun 09 '11 at 13:07

0

Select avg (field_a ) from table_xxx where field_a is not null group by id Will this give you a good example? I'm asking this because the query you mentioned looks odd (is your table normalized?)

answered Jun 09 '11 at 13:07

Dan

1,927
2
24
35

Obtain average no-NULL fields in query

4 Answers4