Obtain average no-NULL fields in query

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   开发者_C百科   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

• 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

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

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

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))

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?)