Clever Geek Handbook

Calculating the number of minutes in a request

I'm looking for a way to count the number of minutes that each player (or just one) on my team played. A simplified database table looks like this:

matchid    action    minute    player
-------------------------------------
1          subbedin  30        Pele 
2          starter             Pele
2          subbedout 50        Pele
3          subbedin  70        Pele
3          red       80        Pele
4          starter             Pele

The request I have now for other characteristics:

$query = mysql_query("SELECT *,
  SUM(CASE WHEN action = 'starter' OR action = 'subbedin' THEN 1 ELSE 0 END) AS games,
  SUM(CASE WHEN action = 'goal' OR action = 'pengoal' THEN 1 ELSE 0 END) AS goals,
  SUM(CASE WHEN action = 'yellow' THEN 1 ELSE 0 END) AS yellows,
  SUM(CASE WHEN action = 'red' THEN 1 ELSE 0 END) AS reds,
  // MINS GOES HERE
FROM league2012
GROUP BY player");

For each match, a basic calculation

( 90 OR subbedout OR red ) - ( starter OR subbedin )

For example, in match 2

subbedout (50) - starter (0) = 50

At the end, the table should look like this:

player    minutes    goals, cards, etc.
---------------------------------------
Pele      210        ...

I've been going through tutorials for the past hour and can't figure out how to do this.

+3


source to share


2 answers 


  sum
    (
      case action
        when 'subbedin'  then 90 - minute
        when 'starter'   then 90
        when 'subbedout' then minute - 90
        when 'red'       then minute - 90
      end
    ) as minutes


+3


source


First, I calculated the minutes that each player played in each match and added them to get the total for the player. To combine the results obtained with the other statistical calculations that you are calculating, I see no other way other than to do other statistics in the same way, that is, first for each player and match, and then for each player. This is what I mean:



SELECT
  player,
  SUM(games) AS games,
  SUM(goals) AS goals,
  SUM(yellows) AS yellows,
  SUM(reds) AS reds,
  SUM(minutesplayed) AS minutesplayed
FROM (
  SELECT
    player,
    matchid,
    SUM(CASE WHEN action IN ('starter', 'subbedin') THEN 1 ELSE 0 END) AS games,
    SUM(CASE WHEN action IN ('goal', 'pengoal') THEN 1 ELSE 0 END) AS goals,
    SUM(CASE WHEN action = 'yellow' THEN 1 ELSE 0 END) AS yellows,
    SUM(CASE WHEN action = 'red' THEN 1 ELSE 0 END) AS reds,
    IFNULL(SUM(CASE WHEN action IN ('subbedout', 'red') THEN minute END), 90)
    - IFNULL(SUM(CASE WHEN action = ('subbedin') THEN minute END), 0) AS minutesplayed
  FROM league2012
  GROUP BY
    player,
    matchid
) s
GROUP BY
  player
+1


source






More articles:


All Articles