calculation in a query

[shaddf]

i have this table:

+-----------+-------+-------------------------+---------------------+
| player_id | Goals | gme                     | nem                 |
+-----------+-------+-------------------------+---------------------+
|         1 |     4 | CRYSTAL SA Vs Sebowa FC | Sam Lukoye          |
|         3 |     3 | CRYSTAL SA Vs Sebowa FC | Mulan Babu          |
|        13 |     0 | CRYSTAL SA Vs Sebowa FC | Doughlas Doughlas   |
|         9 |     0 | CRYSTAL SA Vs Sebowa FC | Owori Clinton       |
|         5 |     0 | CRYSTAL SA Vs Sebowa FC | Zimbe Zimbe         |
|        16 |     0 | CRYSTAL SA Vs Sebowa FC | Jordan Jordan       |
|        12 |     0 | CRYSTAL SA Vs Sebowa FC | Muganda Moses       |
|         8 |     0 | CRYSTAL SA Vs Sebowa FC | Gilbert Gilbert     |
|         4 |     0 | CRYSTAL SA Vs Sebowa FC | Nandara Suda        |
|        15 |     0 | CRYSTAL SA Vs Sebowa FC | Mwase Mwase         |
|        11 |     0 | CRYSTAL SA Vs Sebowa FC | Wasswa Wasswa       |
|         7 |     0 | CRYSTAL SA Vs Sebowa FC | Faisal Faisal       |
|        18 |     0 | CRYSTAL SA Vs Sebowa FC | Ofwono Ofwono       |
|        14 |     0 | CRYSTAL SA Vs Sebowa FC | Balotelli Balotelli |
|        10 |     0 | CRYSTAL SA Vs Sebowa FC | Ntwatwa Enoch       |
|         6 |     0 | CRYSTAL SA Vs Sebowa FC | Emma Emma           |
|         2 |     0 | CRYSTAL SA Vs Sebowa FC | Small Sam           |
|        17 |     0 | CRYSTAL SA Vs Sebowa FC | Wambululu Wambululu |
+-----------+-------+-------------------------+---------------------+
18 rows in set (0.00 sec)

how can i get the total goals scored(7)?
how can i get the percentage goals for each player that is perform a percentage calculation in the query to get them from table above?( for Sam :4/7*100)

[requinix]

You can figure out the percentage yourself if you know the total number of goals. With code. Not another query.

 

SELECT SUM(Goals) AS TotalGoals FROM table WHERE gme = 'CRYSTAL SA Vs Sebowa FC'

By the way it's probably bad table design to have the "gme" being a string and a unique identifier - one that doesn't look particularly unique, even.

[shaddf]

You can figure out the percentage yourself if you know the total number of goals. With code. Not another query.

 

SELECT SUM(Goals) AS TotalGoals FROM table WHERE gme = 'CRYSTAL SA Vs Sebowa FC'

By the way it's probably bad table design to have the "gme" being a string and a unique identifier - one that doesn't look particularly unique, even.

 

why is it that when i try this :

SELECT player_id,round(Goals *100/sum(Goals)) as PaddingR,100-round(Goals*100/sum(Goals)) as marginR

from table

it returns only one row and yet i see two in the table above

[gizmola]

When you use any aggregate operation (sum, avg, max, min...) you will get one row per group. Since you have no group by you are getting a total.

 

To get your stat by player, you need to GROUP BY the player.

 

SELECT player_id,round(Goals *100/sum(Goals)) as PaddingR,100-round(Goals*100/sum(Goals)) as marginR
from table
WHERE gme = 'CRYSTAL SA Vs Sebowa FC'
GROUP BY player_id

NOTE: Disregard the specific calculations. I was super tired when I wrote this and just missed the boat entirely, but I concur with Psycho's answer.

 

My query could be made to work in a number of ways, as suggested by Requinix and Psycho. Psycho went the extra mile and showed how you could get one result set performing this calculation for all games rather than just one.

[shaddf]

When you use any aggregate operation (sum, avg, max, min...) you will get one row per group. Since you have no group by you are getting a total.

 

To get your stat by player, you need to GROUP BY the player.

 

SELECT player_id,round(Goals *100/sum(Goals)) as PaddingR,100-round(Goals*100/sum(Goals)) as marginR
from table
GROUP BY player_id

they are already grouped by player_id.And when I try your idea it gives this:

+-----------+----------+---------+
| player_id | PaddingR | marginR |
+-----------+----------+---------+
|         1 |      100 |       0 |
|         3 |      100 |       0 |
+-----------+----------+---------+
2 rows in set (0.09 sec)

 

with no correct calculation.

 

i expect to be getting this:

+-----------+----------+---------+
| player_id | PaddingR | marginR |
+-----------+----------+---------+
|         1 |       57 |      43 |
|         3 |       43 |      57 |
+-----------+----------+---------+
2 rows in set (0.01 sec)

 i replaced  with the total hard coded:

SELECT x.player_id,round(x.Goals*100/7) as PaddingR,100-round(x.Goals*100/7) as marginR

but this will keep changing

 

I wonder why tjis is happening?

[requinix]

If you can't do what I said earlier about getting the total goals and figuring out the percentage in code then you need to do a subquery because you can't GROUP BY only part of a resultset.

SELECT x.player, x.Goals / y.TotalGoals...
JOIN (SELECT gme, SUM(Goals) AS TotalGoals FROM table GROUP BY gme) y...

Oh. And you need to consider what happens if there were no goals made during the game.

[Psycho]

Your table structure appears to be malformed. The last column appears to be the player name which is associated with the player id in the first column. Plus, the gme column appears to be the "name" of a particular match.

 

First, the Player info should be in its own table and you would then only reference the player ID in the table for the match results info. Second, the matches should also be in their own table with unique IDs as well. Then you would reference the match ID - not the name in the match results table (what would happen in your current structure if the same two teams played each other twice?). Third, you should also have another table to store the teams as separate entities.

 

Here is a sample structure:

 

Teams

TeamID | TeamName
----------------------
 1       CRYSTAL SA
 2       Sebowa FC

 

Players

PlayerID | PlayerName
------------------------
 1         Sam Lukoye
 3         Mulan Babu
 13        Doughlas Doughlas
 9         Owori Clinton
 5         Zimbe Zimbe

 

Matches

MatchID | HomeTeamID | VisitingTeamID | MatchDate
--------------------------------------------------
 2         1            2               2017-08-14

MatchResults

MatchID | PlayerID | Goals
--------------------------
 2         1          4
 2         3          3
 2         13         0
 2         9          0
 2         5          0

 

 

With your current structure (which I don't advise) you can get both the total goals for a match as well as the individual totals/percentage with a single query by using the "ROLLUP" modifier. It will create a subtotal after each GROUP BY parameter.
 
Query

SELECT r1.gme, r1.player_id,
       SUM(r1.goals) as player_goals,
       SUM(r1.goals) / (SELECT SUM(goals) FROM results r2 WHERE r1.gme = r2.gme) as player_perc
 
FROM results r1
 
GROUP BY r1.gme, r1.player_id WITH ROLLUP

 
Example Output

gme                     | player_id | player_goals | player_perc
-----------------------------------------------------------------
CRYSTAL SA Vs Sebowa FC    1           4              .57
CRYSTAL SA Vs Sebowa FC    3           3              .43
CRYSTAL SA Vs Sebowa FC    13          0              0
CRYSTAL SA Vs Sebowa FC    9           0              0
CRYSTAL SA Vs Sebowa FC    5           0              0
. . . 
NULL                       NULL        7              1

Edited August 14, 2017 by Psycho