Creating SQL for Table Consolidation and Grouping

I'm a beginner in SQL.

I have a question about number 13 on the SQL practice site below.
I'm quite busy creating SQL for table consolidation and grouping, so
Please tell me how to approach it.
"As shown below, each team's score is displayed for each game. This issue uses the SQL syntax "CASE WHEN" which has not yet been resolved."
http://sqlzoo.net/wiki/The_JOIN_operation/ja

For team1 and team2, I know that I have to aggregate (GROUP BY) the scores from the goal table, but
I don't know how to divide the game and goal tables and how to group them.

So far, it's done.

SELECT ga.mdate, ga.team1
  FROM game ga, goal go1
    WHERE ga.id = go1.matchid
      AND ga.team1 = go1.teamid
    ORDER BY ga.mdate

Dear user20098

Thank you for your reply.

for study, if possible Could you give me an example answer?

The team name and the team name that finished all the matches as follows: I'm ready to serve...
I don't know how to aggregate team1 and team2 scores using [CASE WHEN].
(Only the bottom of the points you suggested are completed.)

SELECT ga.mdate, ga.team1, ga.team2, go.teamid as goalTeam
FROM game ga
LEFT OUTER JOIN GO GO ON ga.id = go.matchid
ORDER BYga.mdate, ga.id

sql

2022-09-30 11:24

1 Answers

# I don't think the purpose of the page is acceptable, so I will avoid answering the correct answers.Just points.

Basically, I think you should expand the example on the page.I think the three points are as follows:

I think the SQL statement says CASE WHEN for team1, but it also says team2 in the same way
Use SQL statements (results of select statements) as temporary tables and select the temporary table to aggregate (sum) with score1 and score2 in the temporary table.
There may be no score for both teams (no record in the goal table), so game and goal will not produce such a match, so try to combine the two tables

Note: SQL on page.

SELECT mdate,
  team1,
  CASE WHEN teamid= team1 THEEN1 ELSE0 END score1
  FROM game JOIN goal ON matchid=id

I hope it will be helpful.

The SQL statement is determined to be correct.

SELECT mdate, team1, sum(score1)score1, team2, sum(score2)score2 FROM(
    SELECT mdate,
      team1,
      CASE WHEN teamid=team1THEN1ELSE0END score1,
      team2,
      CASE WHEN teamid= team2 THEN1 ELSE0 END score2
      FROM game LEFT OUTER JOIN GOAL ON matchid=id
)t
group by mdate, team1, team2
order by mdate

# I think there are other ways to do this, but if you extend the SQL example according to the hints in the question sentence, you will get this SQL statement.

2022-09-30 11:24

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656