CODEWITHSUNDEEP
mysqlsqlsql-updateaveragesql-insert
Craig Hickey
Craig Hickey

Reputation: 48

Insert A Value Into A New Column In A Table, Calculated From Data From Same Table

I'm asking on behalf of a fried who would like to do a query that calculates the average time for winning a race when selecting 4 columns(COURSE, DISTANCE, GOING & CLASS), grouping those columns and ordering those columns. The calculated average then inserted into the same table. Is this possible?

He's trying to use this kind of SQL statement, which is obviously not one statement as I'm not sure how to put it together.

Thanks for any help!

INSERT 
INTO Table1 (AVGTIMECOLUMN) VALUES (AVERAGE)
SELECT Table1.COLUMN1, Table1.COLUMN2, Table1.COLUMN3, Table1.COLUMN4 Avg(Table1.TIME) AS AVERAGE, 
FROM Table1
GROUP BY Table1.COLUMN1, Table1.COLUMN2, Table1.COLUMN3, Table1.COLUMN4
ORDER BY Table1.COLUMN1, Table1.COLUMN2, Table1.COLUMN3, Table1.COLUMN4;

Or Do i need to do an UPDATE query and set AVG_WEIGHT with the calculation only?

Upvotes: 1

Views: 2532

Answers (2)

TUSHAR DHAMANKAR
TUSHAR DHAMANKAR

Reputation: 31

CREATE TABLE D_JOBS AS 

SELECT JOB_ID, JOB_TITLE, MAX_SALARY, MIN_SALARY,(MAX_SALARY-MIN_SALARY)AS DIFF_SALARY FROM CPY_JOBS

WHERE (MAX_SALARY-MIN_SALARY) >0

Initially 

Table name = CPY_JOBS; that containing (job_id, job_title, max_salary, min_salary)

I wanted to add a new column as difference_salary and that should remain permanent so i have created a new table as D_JOBS and fetched all the data from CPY_JOBS with (max_salary-min_salary) calculation

I think you will get an idea from query thank you.

Upvotes: 0

Tom H
Tom H

Reputation: 47392

If you really needed to be able to do this, then yes you would use an UPDATE statement (if I understand your question correctly). More importantly though, in 99 cases out of 100 you shouldn't be doing this at all.

One of the primary rules in database design is that you shouldn't store redundant data. Besides taking up extra space, it also has problems with the data becoming inconsistent. For example, someone goes back to the table and updates one of the columns on which the derived column is based but doesn't update the derived column. Now it's wrong. In some cases you end up with a situation where you don't know which set of data to believe.

If MySQL supports calculated columns then you could define one on the table. Otherwise you should typically be calculating this value at run time.

Sometimes a database is denormalized to store redundant data for performance reasons. Unfortunately, in most cases the developer(s) do this without first confirming that there actually is a performance problem that can't be fixed in some other way. Most developers who want to use this pattern are sure that theirs is the special case where they should ignore this rule of design. But in my experience, in most cases they are wrong.

Upvotes: 1

Related Questions