Descriptions of tables
player - Contains player's name and id
game_player - Contains the player and what game they played in and for how long.
weapon_t - Contains a table of what weapon what player used to kill anyone per game.
pid = player's id
gid = game's id
wid = weapon's id
I want to make a table that has the following.
player's name, player's id, total time active, total kills
The best SQL command I got is the following.
SELECT player.name, player.pid, SUM(weapon_t.kills) as kills, SUM(game_player.ptimeactive) AS timeactive FROM player LEFT JOIN game_player ON game_player.pid = player.pid LEFT JOIN weapon_t ON player.pid = weapon_t.pid GROUP BY player.pid
This command though gives me inaccurate results for kills and timeactive. Probably from a doubling effect or something.
without subqueries (version 4.1) your best bet is to do something like this:
, 'timeactive' as quantity
, sum(game_player.ptimeactive) as amount
if you're not on 4.0 you will have to run the two queries separately