1

This is my MySQL table feedback.

Mysql Table

I want to calculate average of every column and store the result to table average as below structure using a PHP file.

enter image description here

I am using query SELECT AVG (waiting) FROM feedback to calculate average of waiting column. But I do not have any idea of how to put this query result to another table as the above structure.

When using query INSERT INTO average (average) SELECT AVG (waiting) FROM feedback **it only updating the average column.

Please help me with a sample code.

Thanks

Improve this question

3 Answers 3

Reset to default
1

If understand you correctly you can do it like this

INSERT INTO average (data, average)
SELECT 'waiting',      AVG (waiting)      FROM feedback UNION ALL
SELECT 'consultation', AVG (consultation) FROM feedback UNION ALL
SELECT 'preoperative', AVG (preoperative) FROM feedback

Sample output:

|         DATA | AVERAGE |
--------------------------
|      waiting |       3 |
| consultation |       2 |
| preoperative |       3 |

Here is SQLFiddle demo

Now if you don't have lots of data in feedback table you can ditch average table and create a view with the same way. As a result instead of maintaining a table of averages they will be calculated on the fly when you select from it

CREATE VIEW average AS 
SELECT 'waiting',   ROUND(AVG (waiting)) average FROM feedback UNION ALL
SELECT 'consultation', ROUND(AVG (consultation)) FROM feedback UNION ALL
SELECT 'preoperative', ROUND(AVG (preoperative)) FROM feedback

And use it

SELECT * FROM average

Here is SQLFiddle demo

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

it returns this error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3
Did you ditched ... from the end of the query? It meant that you can continue to add other averages as you need. See my sqlfiddle example that I provided.
You don't need an extra UNION ALL at the end. Delete it and try again, please.
Perfect. Now I hope I can use this query from a php everytime user updates the feedback database. Actually I want to show this average data as a googlegraph.
Your second suggestion is a very good option, but I think it will not be useful in my solution. As I want to call the data from average table from a separate php to show the data as a graph.
0

To get average values for those three columns:

SELECT AVG(waiting),AVG(consultation),AVG(preoperative) FROM feedback;

To insert to average table, if column names are the same:

INSERT INTO average(waiting,consultation,preoperative) 
SELECT AVG(waiting),AVG(consultation),AVG(preoperative) FROM feedback
Improve this answer

Comments

0

You can grab the result from the average of each column and then INSERT that into a separate database.

What you are doing now inserts it into the same column in the same database, not a different one, which is why it's just updating that column and not a different one.

Example of an INSERT query:

$query = "INSERT INTO tableName (columnName) VALUES ($averageOfColumn)";

Replace 'tableName' with the name of the new table you are connecting to.

Replace 'columnName' with the name of the column in the other database you are using.

Replace '$averageOfColumn' with your variable for the average of the waiting column.

Does this help?

Improve this answer

2 Comments

@PeeJay great. Make sure to create a separate table!
Sure Sir. Its late here, So I will definitely answer you tomorrow.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.