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.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
Question about Averaging IF() function results
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 [EMAIL PROTECTED]
RE: Why is the average of an int column returned as a string
I have tried that and that seems to be working for me. I am just wondering why all of the sudden queries that were returning the averages as numeric values as string values. Thank you for your suggestion. 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] -Original Message- From: Rhys Campbell [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 22, 2007 3:34 AM To: Eric Lommatsch; mysql@lists.mysql.com Subject: RE: Why is the average of an int column returned as a string You could use the CAST function, although I have just discovered that MySQL is rather limited in the types you are able to CAST to... http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html -Original Message- From: Eric Lommatsch [mailto:[EMAIL PROTECTED] Sent: 21 August 2007 22:52 To: mysql@lists.mysql.com Subject: Why is the average of an int column returned as a string Hello, I am working with a query that calculates the averages of survey answers. The survey answers are stored in the database as int(11) fields. When I run the query the results that I am getting are being returned as string data. The query that I am working with is a data source for a Crystal Reports reports. The average columns that are being returned by the query are used in the report in fields that have been formatted for double values. I am using MySQL 5.0.18 as the database. What would I have to do to get the averages of Int columns to return as doubles, rather then having to change all of my columns to be double columns? 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] This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why is the average of an int column returned as a string
Hello, I am working with a query that calculates the averages of survey answers. The survey answers are stored in the database as int(11) fields. When I run the query the results that I am getting are being returned as string data. The query that I am working with is a data source for a Crystal Reports reports. The average columns that are being returned by the query are used in the report in fields that have been formatted for double values. I am using MySQL 5.0.18 as the database. What would I have to do to get the averages of Int columns to return as doubles, rather then having to change all of my columns to be double columns? 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]
Undelete a droped table
Hello, I have accidently droped a table from one of my database's. The table was an innodb table. Is there anyway that I can recover the table or is that information gone for good if I don't have a current backup of that table? Thank you Eric H. Lommatsch Programmer MICRONix, Inc. 2087 South Grant Street Denver, CO 80210 Tel 303-777-8939 Fax 303-778-0378 [EMAIL PROTECTED]
Installing MySQL 4.1.12 on Mandrake 10.1
Hello List, I have a new test Linux server with Mandrake 10.1 installed that I was trying to install MySQL version 4.1.12 on today. Once I had installed the software, whenever I tried to start MySQL on this server I was getting the error message "[ERROR] Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist" when I tried to run the 'mysql_setpermissions' script and was getting the same result. After this failed I decided to try the MySQL installation files that came with the operating system which were Version 4.0.20 and this version worked fine. When I was installing the 4.1.12 version I was simply going with a minimal install of just the server package and the clients. When I ran the installation package from Mandrake it installed other files that from my reading of the MySQL manual I did not think that I needed. My questions are: Is there other files besides just the server and the client that I would need to install to get MySQL to work on Mandrake v10.1? Are there other issues with 4.1.12 that I have not fully explored that might prevent this from being installed? Finally if I upgraded this system to 4.1.12 now that 4.0.20 is installed would I wind up having the same problems? Thank you Eric H. Lommatsch Programmer MICRONix, Inc. 2087 South Grant Street Denver, CO 80210 Tel 303-777-8939 Fax 303-778-0378 [EMAIL PROTECTED]
Corrupted .MYI file
Hello, We had a situation over night where users were getting the error message: "[My SQL][ODBC 3.51 Driver][mysqld-4.0.13-nt] Can't open file: flex. MYI: [error : 145]" I found that .MYI file had become corrupted and I repaired the table and things are working as they should now. However the reason that I am sending this email is that my end users want to know why this index had become corrupted. My first thought is that something had happened while a record was being writen to the table which caused the index to be corrupted. Does anyone have any other suggestions as to why the index file for a table might become corrupted so that I can explain this to my end users. Thank you Eric H. Lommatsch Programmer MICRONix, Inc. 2087 South Grant Street Denver, CO 80210 Tel 303-777-8939 Fax 303-778-0378
Conditional controlling where used in a query
I have two tables that I want write one query that uses different where clauses under conditions in the where clause. In the first table I have type of document with document names. Through a third party program I am passing a document name as a parameter. This table also has a key that is called DoctypeID. The second table lists the possible subfolders that the documents can be stored in, which is located based on matching the DoctypeID. Certain document types can only go into one specific subfolder. i.e. a "New Hire" document can only go into the "New Hire" subfolder and "Payroll" documents can only go into the "Payroll" subfolder. However other documents can go into any subfolder. i.e. "email documents". The first attempt at writing this query is below. select distinct subfolderdescription from doctype a, subfolddesc b where if(a.doctype = "HR" and a.doctypeid = b.doctypeid, a.doctype = "HR" and a.doctypeid = b.doctypeid, b.doctypeid like "%"); This did not work the way that I intended for it to work. What I want to do is have one query that will find the record that matches the document type in the first table and if it finds a match for that record in the second table return subfolder for the matching record. But if it does not find a match to return the complete list of all the unique subfolders that are in the second table. Does anyone have any ideas on how to do this? Thank you Eric H. Lommatsch Programmer MICRONix, Inc. 2087 South Grant Street Denver, CO 80210 Tel 303-777-8939 Fax 303-778-0378
