List:General Discussion« Previous MessageNext Message »
From:Eric Lommatsch Date:November 4 2008 7:47pm
Subject:Question about Averaging IF() function results
View as plain text  
Hello List,
 
I have a question about trying to calculate an average across columns. I am
trying to calculate the results of surveys where in the data I have
individuals that have marked questions on the survey as N/A. in my survey I
am using 1-6 as the evaluated answers and if the person marked NA the stored
value is 7.
 
Here is a table with some sample data of what I am using to test the
calculation I am working on:  ( actually this is simplified from the actual
data but the results I get are still the same)
 
CREATE TABLE `avgTest` (
  `Course` varchar(8) default NULL,
  `Q1` int(11) default NULL,
  `Q2` int(11) default NULL,
  `Q3` int(11) default NULL,
  `Q4` int(11) default NULL,
  `Q5` int(11) default NULL
)    
 
Course    |    Q1    |    Q2    |    Q3    |    Q4    |    Q5    
-----------------------------------------------------------------------------
----
HUM300  |    6      |     6      |     7     |      6     |    6
HUM301  |    6      |     6      |     6     |      6     |    6
HUM301  |    7      |     7      |     7     |      7     |    7
 
Here is the query that I am using to perform the calculations
 
select course,
  avg(IF(avgTest.Q1<7,avgTest.Q1,Null)) as AvgOfQ1,
  avg(IF(avgTest.Q2<7,avgTest.Q2,Null)) as AvgOfQ2,
  avg(IF(avgTest.Q3<7,avgTest.Q3,Null)) as AvgOfQ3,
  avg(IF(avgTest.Q4<7,avgTest.Q4,Null)) as AvgOfQ4,
  avg(IF(avgTest.Q5<7,avgTest.Q5,Null)) as AvgOfQ5,
  (avg(IF(avgTest.Q1<7,avgTest.Q1,Null))
  +avg(IF(avgTest.Q2<7,avgTest.Q2,Null))
  +avg(IF(avgTest.Q3<7,avgTest.Q3,Null))
  +avg(IF(avgTest.Q4<7,avgTest.Q4,Null))
  +avg(IF(avgTest.Q5<7,avgTest.Q5,Null)))/5 as overallAvg from avgTest group
by course;
 
Here are the results that I get that are incorrect.
 
Course    |    AvgOfQ1    |    AvgOfQ2    |    AvgOfQ3    |    AvgOfQ4    |
AvgOfQ5    |    overallAvg
-----------------------------------------------------------------------------
----------------------------------------------------------------------
HUM300  |      6.000      |        6.000     |       Null        |
6.000      |        6.000     |       Null 
HUM301  |      6.000      |        6.000     |      6.000      |       6.000
|        6.000     |       6.000 
 
Here are the results that I get that when I change using null in the query to
a 0.
 
Course    |    AvgOfQ1    |    AvgOfQ2    |    AvgOfQ3    |    AvgOfQ4    |
AvgOfQ5    |    overallAvg
-----------------------------------------------------------------------------
----------------------------------------------------------------------
HUM300  |      6.000      |        6.000     |      0.000      |       6.000
|        6.000     |       4.800 
HUM301  |      6.000      |        6.000     |      6.000      |       6.000
|        6.000     |       6.000 
 
Here are the results that I want to be getting from the query that I am
working with.
 
Course    |    AvgOfQ1    |    AvgOfQ2    |    AvgOfQ3    |    AvgOfQ4    |
AvgOfQ5    |    overallAvg
-----------------------------------------------------------------------------
----------------------------------------------------------------------
HUM300  |      6.000      |        6.000     |       Null        |
6.000      |        6.000     |       6.000 
HUM301  |      6.000      |        6.000     |      6.000      |       6.000
|        6.000     |       6.000 
 
I tried using the if function without a false answer and I am getting a
syntax error when I do this.
 
If it is possible for me to get this correct result in MySQL, can someone
provide me with the correct query syntax to get these results?
 
 
Thank you
 
Eric H. Lommatsch
Programmer
360 Business 
2087 South Grant Street
Denver, CO 80210
Tel 303-777-8939
Fax 303-778-0378
 
ericl@stripped

 


Attachment: [text/html]
Attachment: [text/html]
Thread
Question about Averaging IF() function resultsEric Lommatsch4 Nov
  • Re: Question about Averaging IF() function resultsPeter Brawley4 Nov
RE: Question about Averaging IF() function resultsEric Lommatsch5 Nov