Muhammad Tauseen - 1 year ago 55
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
``````

``````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
};
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download