Muhammad Tauseen Muhammad Tauseen - 4 months ago 9
SQL Question

Count and take average of not null columns in a single row

I want to count and take average of not null columns in each row
for example, i have a table like this

Name | a | b | c | d | e | f |
| | | | | | |
umar | 2 |null| 3 | 5 | null| 4 |
ali |null|null| 3 |null| 1 | 4 |
ali |null|null| 3 |null| null| 4 |


the result should be

Name | a | b | c | d | e | f | average
| | | | | | |
umar | 2 |null| 3 | 5 | null| 4 | 3.5
ali |null|null| 3 |null| 1 | 4 | 2.66
ali |null|null| 3 |null| null| 4 | 3.5

Answer
var query = from x in table
            select new 
            {
                CountNotNull = (x.a ?? 0) + (x.b ?? 0) + (x.c ?? 0) + (x.d ?? 0) + (x.e ?? 0) + (x.f ?? 0) 
            };

If you don't want to count it but calculate the average(you've changed the question):

var query = 
     from x in table
     let NotNullColCount = (x.a == null ? 0 : 1) + (x.b == null ? 0 : 1) + (x.c == null ? 0 : 1) + (x.d == null ? 0 : 1) + (x.e == null ? 0 : 1) + (x.f == null ? 0 : 1)
     let NotNullColSum = (x.a ?? 0) + (x.b ?? 0) + (x.c ?? 0) + (x.d ?? 0) + (x.e ?? 0) + (x.f ?? 0)
     select new 
     {
        AverageNotNull = NotNullColCount == 0 
                         ? 0.0 
                         : 1.0 * NotNullColSum / NotNullColCount 
     };