Calculating multiple column average in SQLite3
- by Benjamin Oakes
I need to average some values in a row-wise fashion, rather than a column-wise fashion. (If I were doing a column-wise average, I could just use avg()). My specific application of this requires me ignore NULLs in averaging. It's pretty straightforward logic, but seems awfully difficult to do in SQL. Is there an elegant way of doing my calculation?
I'm using SQLite3, for what it's worth.
Details
If you need more details, here's an illustration:
I have a a table with a survey:
| q1 | q2 | q3 | ... | q144 |
|----|-------|-------|-----|------|
| 1 | 3 | 7 | ... | 2 |
| 4 | 2 | NULL | ... | 1 |
| 5 | NULL | 2 | ... | 3 |
(Those are just some example values and simple column names. The valid values are 1 through 7 and NULL.)
I need to calculate some averages like so:
q7 + q33 + q38 + q40 + ... + q119 / 11 as domain_score_1
q10 + q11 + q34 + q35 + ... + q140 / 13 as domain_score_2
...
q2 + q5 + q13 + q25 + ... + q122 / 12 as domain_score_14
...but i need to pull out the nulls and average based on the non-nulls. So, for domain_score_1 (which has 11 items), I would need to do:
Input: 3, 5, NULL, 7, 2, NULL, 3, 1, 5, NULL, 1
(3 + 5 + 7 + 2 + 3 + 1 + 5 + 1) / (11 - 3)
27 / 8
3.375
A simple algorithm I'm considering is:
Input:
3, 5, NULL, 7, 2, NULL, 3, 1, 5, NULL, 1
Coalesce each value to 0 if NULL:
3, 5, 0, 7, 2, 0, 3, 1, 5, 0, 1
Sum:
27
Get the number of non-zeros by converting values 0 to 1 and sum:
3, 5, 0, 7, 2, 0, 3, 1, 5, 0, 1
1, 1, 0, 1, 1, 0, 1, 1, 1, 0, 1
8
Divide those two numbers
27 / 8
3.375
But that seems like a lot more programming than this should take. Is there an elegant way of doing this that I'm not aware of?
Update:
Unless I'm misunderstanding something, avg() won't work for this. Example of what I would want to do:
select avg(q7, q33, q38, ..., q119) from survey;
Output:
SQL error near line 3: wrong number of arguments to function avg()
