CODEWITHSUNDEEP
mysql
Icefire
Icefire

Reputation: 163

Mysql: Combine 2 queries in one (both have their own group by...)

I'm trying to make only one query out of the following, but can't find a way to do it (The thing is the hard coded date is depending on a grand parent column right now so as mysql won't admit that I'm trying to do that in 1 query instead of creating a temporary table just for the sake of it.

select sum(result) 
from 
(
    SELECT 
      round(SUM(sr3.F_RESULT)/count(distinct(sr3.f_subgame_id))) as result,
      sr3.f_team_id
    FROM t_subgames_results sr3
    JOIN t_subgames s3 on s3.f_subgame_id = sr3.f_subgame_id
    JOIN t_seasons_games sg3 on sg3.f_game_id = s3.f_game_id
    WHERE sg3.f_date < '2012-09-14'
      and sr3.f_result>-1
    GROUP BY sr3.f_player_id
) averageByPlayer
group by f_team_id;

As you see, I need to get average results by game by team, but the average has to be calculated on each player (and round up) before the sum by team.

Is there a way to make it only one query ?

Upvotes: 1

Views: 62

Answers (1)

MvG
MvG

Reputation: 61077

There is no way to do what you want to do without either a subquery or two completely distinct tables. Each SELECT can only aggregate over one set of groups, not two nested level of groups.

However, there may be a way to avoid the use of a temporary table. EXPLAIN might show you the execution plan, how that query actually gets executed. If there is no mention of a temporary table there, then the MySQL engine already optimized that away. Otherwise, perhaps a slight modification of the query might help. E.g. you could try to include f_team_id in the inner GROUP BY, preferrably before f_player_id.

If that doesn't work, you might want to provide some sample data on SQL Fiddle so users can experiment with that in an attempt to find a formulation which does not use a temporary table. Make sure to include realistic amounts data, as optimization might take the number of table rows into account.

Upvotes: 1

Related Questions