CODEWITHSUNDEEP
phpmysqlcountcase
Awesom-o
Awesom-o

Reputation: 612

php/mysql - counting and grouping

I have a database with played games and their scores. Now i want to run an query that counts all the results for each team with over x scored goals total (home and away combined).

Database structure:

+----------+----------+---------------+---------------+
| HomeTeam | AwayTeam | HomeTeamGoals | AwayTeamGoals |
+----------+----------+---------------+---------------+
| team1    | team2    |             3 |             1 |
| team3    | team4    |             1 |             2 |
| team1    | team3    |             4 |             4 |
| team4    | team2    |             0 |             1 |
+----------+----------+---------------+---------------+

Wanted results:

+-------+----------+--------------------+
| team  | played   |  games > 3.5 goals |
+-------+----------+--------------------+
| team1 |        2 |                  2 |
| team2 |        2 |                  1 |
| team3 |        2 |                  1 |
| team4 |        2 |                  0 |
+-------+----------+--------------------+

I was looking at something like this:

SELECT *, COUNT(*) AS total FROM games WHERE homeTeamGoals + awayTeamGoals > 3.5 GROUP BY homeTeam

But that only does it for the home games, so i need a way to also add the away games to this.

Upvotes: 1

Views: 55

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

  • Get results for a team as HomeTeam and AwayTeam separately.
  • Combine the results using UNION ALL
  • Use the results as a Derived Table.
  • Group by on the Derived Table for a team, to get the results.

Try the following (SQL Fiddle DEMO):

SELECT derived_t.team, 
       SUM(derived_t.played) AS played, 
       SUM(derived_t.games)  AS 'games > 3.5 goals'
FROM 
(
  SELECT t1.HomeTeam as team, 
         COUNT(*) AS played, 
         SUM( IF(t1.HomeTeamGoals + t1.AwayTeamGoals > 3.5, 1, 0) ) AS games  
  FROM your_table AS t1 
  GROUP BY t1.HomeTeam

  UNION ALL 

  SELECT t1.AwayTeam as team, 
         COUNT(*) AS played, 
         SUM( IF(t1.HomeTeamGoals + t1.AwayTeamGoals > 3.5, 1, 0) ) AS games  
  FROM your_table AS t1 
  GROUP BY t1.AwayTeam
) AS derived_t
GROUP BY derived_t.team

Upvotes: 1

Related Questions