phpmyadmin Question posted in
The official documentation can be found here.

average of derived attributes in new table – Phpmyadmin

How can I calculate the average of a person (in this case player) x & y position whilst creating a new table and adding said average to a new column.

CREATE TABLE PlayerStatistics  AS SELECT
    PLAY_Name
FROM
    player;

ALTER TABLE
    PlayerStatistics ADD AveragePosition DECIMAL(6, 5)
SELECT
    AVG(
         Player1(T1) - X,
         Player1(T1) - Y
    ))
FROM
    tracksdataview

The end result of the code is a new table with one column of the player’s name/id and another column that has an average value of both the x and y positions in each row.

Tags:

2

Answers


  1. 0

    Depending on your DBMS, you may be able to combine your calculation and CREATE TABLE statement.

    CREATE TABLE PlayerStatistics AS
SELECT 
  PLAY_Name, 
  CAST((Player_X + Player_Y) / 2 AS DECIMAL(6,5)) AS AveragePosition
FROM player p
LEFT JOIN tracksdataview tdv ON p.play_name = tdv.play_name -- Get track data (if any)
;

    You may need to CAST the x, y values as FLOAT before doing the division. Give it a try and let me know.

    Login or Signup to reply.
  2. 0

    I suspect, that a new table might not be the best solution to your problem. Consider the case where a position X or Y changes over time. This will then not be reflected in your “derived” attributes in the separate table.

    My suggestion would be to generate a view that will always “look” at the original table:

    CREATE VIEW PlayerStatistics  AS 
SELECT *, ax-X devX, ay-Y devY
FROM tracksdataview t
INNER JOIN (SELECT playerId, AVG(X) ax, AVG(Y) ay FROM tracksdataview GROUP BY playerId) ta
ON ta.playerId=t.playerId

    As I was uncertain about the type of “average” you want I calculated an average over all positions of a particular player and then created two columns showing the player’s x- and y- deviations from their average positions.

    (I also made the assumption that an ID-columns (playerId) exists …)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search