List:General Discussion« Previous MessageNext Message »
From:Eric Lommatsch Date:November 4 2008 11:00pm
Subject:RE: Question about Averaging IF() function results
View as plain text  
Hello Peter,
 
Thanks for your suggestion, I think I have found another way to get the
average that I need. 
 
If the formula I have come up with does not work I will try your formula.
 
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
 

________________________________

From: Peter Brawley [mailto:peter.brawley@stripped] 
Sent: Tuesday, November 04, 2008 1:14 PM
To: Eric Lommatsch
Cc: mysql@stripped
Subject: Re: Question about Averaging IF() function results


Eric,

I'd replace ....

  (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;

with ...

(IF(avgTest.Q1<7,avgTest.Q1,0) + IF(avgTest.Q2<7,avgTest.Q2,0) +
IF(avgTest.Q3<7,avgTest.Q3,0)+
(IF(avgTest.Q1<7,avgTest.Q1,0)+ IF (avgTest.Q2<7,avgTest.Q2,0)+ IF
(avgTest.Q3<7,avgTest.Q3,0)+
IF(avgTest.Q4<7,avgTest.Q4,0) + IF(avgTest.Q5<7,avgTest.Q5,0)) /
MAX(1,IF(avgTest.Q1<7,1,0) + IF(avgTest.Q2<7,1,0) + IF(avgTest.Q3<7,1,0) +
IF(avgTest.Q4<7,1,0) + IF(avgTest.Q5<7,1,0))

PB

----

Eric Lommatsch wrote: 

	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

	 

	
________________________________



	No virus found in this incoming message.
	Checked by AVG - http://www.avg.com 
	Version: 8.0.175 / Virus Database: 270.8.5/1764 - Release Date:
11/3/2008 7:46 AM
	
	  


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