PHP / SQL - Get sum of another column

Question

I have two tables in my MySQL database. One is called players and the other table is called worlds. Each player has a "level" and a "world" column. What I want to achieve is to fill up the columns in the table world based on the column values from table players. I want to first of all get the sum of players on that world and also an average of their levels.

My tables look like this:

Table: Players

+-------+-------+--------+----------+-------+--------------+---------+--------+
|  id   | name  |  sex   | vocation | level | achievements |  world  | online |
+-------+-------+--------+----------+-------+--------------+---------+--------+
| 24471 | John  | Male   | None     |    92 |            3 | Antica  |      1 |
| 24493 | Bob   | Male   | None     |    76 |           19 | Amera   |      0 |
| 24535 | Sam   | Male   | None     |    75 |            0 | Antica  |      0 |
| 24574 | Sarah | Female | None     |    78 |           23 | Beneva  |      1 |
| 24673 | Carl  | Male   | None     |    75 |           10 | Belobra |      1 |
+-------+-------+--------+----------+-------+--------------+---------+--------+

Table: Worlds

+----+---------+---------+--------+--------+----------+---------------+--------------+
| id |  name   | players | online | avglvl | totalexp |   location    |     pvp      |
+----+---------+---------+--------+--------+----------+---------------+--------------+
|  1 | Amera   |       0 |      0 |      0 |        0 | North America | Open PvP     |
|  2 | Antica  |       0 |      0 |      0 |        0 | Europe        | Open PvP     |
|  3 | Astera  |       0 |      0 |      0 |        0 | North America | Optional PvP |
|  4 | Belobra |       0 |      0 |      0 |        0 | South America | Optional PvP |
|  5 | Beneva  |       0 |      0 |      0 |        0 | Europe        | Optional PvP |
+----+---------+---------+--------+--------+----------+---------------+--------------+

So for example, we can see that the players John and Sam have the world Antica. Therefore, Antica should have the value "2" under the player column in the table world. But only John is online on Antica. So the world Antica should have the value "1" under the column "online" in the table "world". And so on. I also want their average level on that world.

How can I achieve this with PHP by making an SQL query?

First I need to get all the world names (I think?) and loop through them. And then get the following details for each world:

Player amount
Online amount
Average level amount

And then make an update in to worlds table with those values..

Your question is too broad. Can you post something that you tried and it may have failed you? That way it will at least shown some effort on your part. — Funk Forty Niner
– Funk Forty Niner, Commented Dec 28, 2017 at 18:07

Gordon Linoff · Accepted Answer · 2017-12-28 18:10:30Z

1

You can summarize the data as:

select world, count(*) as players, sum(online) as online, avg(level) as avglevel
from players
group by world;

You can incorporate this into an update:

update worlds w join
       (select world, count(*) as players, sum(online) as online, avg(level) as avglevel
        from players
        group by world
       ) p
       on w.world = p.world
    set w.players = p.player,
        w.online = p.online,
        w.avglevel = p.avglevel;

I don't think maintaining a separate table like this is a good idea. After all, you can always just query players to get this information. Also the two systems can quickly get out-of-date, so the data is inconsistent. You would fix that by using triggers, but that adds complexity to the system. In general, a simple query is quite sufficient.

answered Dec 28, 2017 at 18:10

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

2 Comments

Lee Cheung Over a year ago

My man! You solved it! I am going to use a cron job for this anyways so it will never be out of date. Thank you so much! I freaking love you, dude! I just found a few typos in your query, but I fixed it. Should be on w.name = p.world and set w.players = p.players. Thank you so sooooooooo much!

Gordon Linoff Over a year ago

@LeeCheung . . . Even with a cron job, the table will be out-of-date. You are better off using a view. If you need this in a table for some reason, then you may need to explore triggers.

Collectives™ on Stack Overflow

PHP / SQL - Get sum of another column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related