I want to know in the year in which more goals were scored (in total), how many goals were scored by and against team 1 when team 1 is either a or b.

My table looks like this:

This is the output that I need:

I know that more goals were scored in year 2 by doing this query:

select year, sum(score_team1   score_team2) as total
from data
group by year
order by sum(score_team1   score_team2) desc
limit(1)

Now I want to know how many goals were scored by and against team1 when team1 is either a or b. I know how to write the queries separately but how can I nest them in one query so I can get the results in one table like the one above?

CodePudding user response：

To obtain the results you desire in a single table, you can use a SQL query with a GROUP BY clause and a HAVING clause to filter for the year in which the most goals were scored. If you are using MySQL, the query might look something like this:

SELECT year, team1, 
  SUM(score_team1) AS max_score_team1, 
  SUM(score_team2) AS max_score_team2
FROM data
GROUP BY year, team1
HAVING year = (SELECT year
               FROM data
               GROUP BY year
               ORDER BY SUM(score_team1   score_team2) DESC
               LIMIT 1)
AND team1 IN ('a', 'b')

The GROUP BY clause groups the results by year and team, and the HAVING clause is used to filter only the results for the year in which the most goals were scored and for team 'a' or 'b'. The nested query in the HAVING clause is used to obtain the year in which the most goals were scored.

The result of the query would be something like this:

CodePudding user response：

Use conditional aggregation to count team #1's goals.

select 
  year,
  sum(score_team1   score_team2) as total,
  sum(case when team1 = 1 then score_team1 else 0 end)  
  sum(case when team2 = 1 then score_team2 else 0 end) as scored_by_team_1,
  sum(case when team1 = 1 then score_team2 else 0 end)  
  sum(case when team2 = 1 then score_team1 else 0 end) as scored_against_team_1
from data
group by year
order by total
limit 1;

The problem with that: If there is more than one year with the top goals count, you'd pick one arbitrarily.

So, instead:

select year, total, scored_by_team_1, scored_against_team_1
from
(
  select 
    year,
    sum(score_team1   score_team2) as total,
    max(sum(score_team1   score_team2)) over () as max_total,
    sum(case when team1 = 1 then score_team1 else 0 end)  
    sum(case when team2 = 1 then score_team2 else 0 end) as scored_by_team_1,
    sum(case when team1 = 1 then score_team2 else 0 end)  
    sum(case when team2 = 1 then score_team1 else 0 end) as scored_against_team_1
  from data
  group by year
) with_max_total
where total = max_total;