Re: Question about Averaging IF() function results
Eric, I'd replace (avg(IF(avgTest.Q17,avgTest.Q1,Null)) +avg(IF(avgTest.Q27,avgTest.Q2,Null)) +avg(IF(avgTest.Q37,avgTest.Q3,Null)) +avg(IF(avgTest.Q47,avgTest.Q4,Null)) +avg(IF(avgTest.Q57,avgTest.Q5,Null)))/5 as overallAvg from avgTest group by course; with ... (IF(avgTest.Q17,avgTest.Q1,0) + IF(avgTest.Q27,avgTest.Q2,0) + IF(avgTest.Q37,avgTest.Q3,0)+ (IF(avgTest.Q17,avgTest.Q1,0)+ IF (avgTest.Q27,avgTest.Q2,0)+ IF (avgTest.Q37,avgTest.Q3,0)+ IF(avgTest.Q47,avgTest.Q4,0) + IF(avgTest.Q57,avgTest.Q5,0)) / MAX(1,IF(avgTest.Q17,1,0) + IF(avgTest.Q27,1,0) + IF(avgTest.Q37,1,0) + IF(avgTest.Q47,1,0) + IF(avgTest.Q57,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.Q17,avgTest.Q1,Null)) as AvgOfQ1, avg(IF(avgTest.Q27,avgTest.Q2,Null)) as AvgOfQ2, avg(IF(avgTest.Q37,avgTest.Q3,Null)) as AvgOfQ3, avg(IF(avgTest.Q47,avgTest.Q4,Null)) as AvgOfQ4, avg(IF(avgTest.Q57,avgTest.Q5,Null)) as AvgOfQ5, (avg(IF(avgTest.Q17,avgTest.Q1,Null)) +avg(IF(avgTest.Q27,avgTest.Q2,Null)) +avg(IF(avgTest.Q37,avgTest.Q3,Null)) +avg(IF(avgTest.Q47,avgTest.Q4,Null)) +avg(IF(avgTest.Q57,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 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 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
RE: Question about Averaging IF() function results
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 [EMAIL PROTECTED] From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 04, 2008 1:14 PM To: Eric Lommatsch Cc: mysql@lists.mysql.com Subject: Re: Question about Averaging IF() function results Eric, I'd replace (avg(IF(avgTest.Q17,avgTest.Q1,Null)) +avg(IF(avgTest.Q27,avgTest.Q2,Null)) +avg(IF(avgTest.Q37,avgTest.Q3,Null)) +avg(IF(avgTest.Q47,avgTest.Q4,Null)) +avg(IF(avgTest.Q57,avgTest.Q5,Null)))/5 as overallAvg from avgTest group by course; with ... (IF(avgTest.Q17,avgTest.Q1,0) + IF(avgTest.Q27,avgTest.Q2,0) + IF(avgTest.Q37,avgTest.Q3,0)+ (IF(avgTest.Q17,avgTest.Q1,0)+ IF (avgTest.Q27,avgTest.Q2,0)+ IF (avgTest.Q37,avgTest.Q3,0)+ IF(avgTest.Q47,avgTest.Q4,0) + IF(avgTest.Q57,avgTest.Q5,0)) / MAX(1,IF(avgTest.Q17,1,0) + IF(avgTest.Q27,1,0) + IF(avgTest.Q37,1,0) + IF(avgTest.Q47,1,0) + IF(avgTest.Q57,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.Q17,avgTest.Q1,Null)) as AvgOfQ1, avg(IF(avgTest.Q27,avgTest.Q2,Null)) as AvgOfQ2, avg(IF(avgTest.Q37,avgTest.Q3,Null)) as AvgOfQ3, avg(IF(avgTest.Q47,avgTest.Q4,Null)) as AvgOfQ4, avg(IF(avgTest.Q57,avgTest.Q5,Null)) as AvgOfQ5, (avg(IF(avgTest.Q17,avgTest.Q1,Null)) +avg(IF(avgTest.Q27,avgTest.Q2,Null)) +avg(IF(avgTest.Q37,avgTest.Q3,Null)) +avg(IF(avgTest.Q47,avgTest.Q4,Null)) +avg(IF(avgTest.Q57,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
