Question

This is the correct answer.

SELECT

c.name AS country,

-- Calculate the percentage of tied games in each season

AVG(CASE WHEN m.season= '2013/2014' AND m.home_goal = m.away_goal THEN 1

WHEN m.season= '2013/2014' AND m.home_goal != m.away_goal THEN 0

END) AS ties_2013_2014,

AVG(CASE WHEN m.season= '2014/2015' AND m.home_goal = m.away_goal THEN 1

WHEN m.season= '2014/2015' AND m.home_goal != m.away_goal THEN 0

END) AS ties_2014_2015

FROM country AS c

LEFT JOIN matches AS m

ON c.id = m.country_id

GROUP BY country;

--------------------------

This is my answer

SELECT

c.name AS country,

-- Calculate the percentage of tied games in each season

AVG(CASE WHEN m.season= '2013/2014' AND m.home_goal = m.away_goal THEN 1 else 0 END) AS ties_2013_2014,

AVG(CASE WHEN m.season= '2014/2015' AND m.home_goal = m.away_goal THEN 1else 0 END) AS ties_2014_2015

FROM country AS c

LEFT JOIN matches AS m

ON c.id = m.country_id

GROUP BY country;

-------------------------------------------

Why I cant change

WHEN m.season= '2013/2014' AND m.home_goal != m.away_goal THEN 0

to else 0?

I mean, the only way to tie a game is both parties have the same score.

Answer 1

The revised SQL query is correct; using 'ELSE 0' in a CASE statement is standard for any case not already specified, making the query more efficient and easier to read.

Step-by-step explanation:

Your revised SQL query is correct and will function the same as the original one given for calculating the percentage of tied games. In SQL, when you are using a CASE statement and expect a binary outcome (1 for a tie, 0 for no tie), specifying the condition for a '1' outcome and using 'ELSE 0' is the standard way to handle all other cases.

By including 'ELSE 0', you ensure that any record not matching the tie condition (home goals equal to away goals) will automatically result in a 0. This simplifies the code and avoids the necessity to explicitly write out the non-tie condition.

Using 'ELSE 0' is a common practice for both readability and efficiency in SQL. The reason is that if a record does not satisfy the first condition of being in the '2013/2014' season with equal goals (a tie), then without needing to check further, it can be assumed to not be a tie, hence a 0 is assigned, covering all non-tie scenarios.