World Cup Database - Build a World Cup Database

Tell us what’s happening:
I completed all the tasks but only the last task keeps telling me that I have not finished my queries.sh correctly…

Did I overlook something? I think the output of these queries is the same as in exepcted_output.txt …

Another question:
Average number of goals in all games from both teams.
Shouldnt that actually be something like this? (my math is not that good maybe)

(AVG(winner_goals) + AVG(opponent_goals)) / 2

Your code so far
#! /bin/bash

PSQL=“psql --username=freecodecamp --dbname=worldcup --no-align --tuples-only -c”

Do not change code above this line. Use the PSQL variable above to query your database.

echo -e “\nTotal number of goals in all games from winning teams:”

echo “$($PSQL “SELECT SUM(winner_goals) FROM games”)”

echo -e “\nTotal number of goals in all games from both teams combined:”

echo “$($PSQL “SELECT SUM(winner_goals) + SUM(opponent_goals) FROM games”)”

echo -e “\nAverage number of goals in all games from the winning teams:”

echo “$($PSQL “SELECT AVG(winner_goals) FROM games”)”

echo -e “\nAverage number of goals in all games from the winning teams rounded to two decimal places:”

echo “$($PSQL “SELECT ROUND(AVG(winner_goals), 2) FROM games”)”

echo -e “\nAverage number of goals in all games from both teams:”

echo “$($PSQL “SELECT AVG(winner_goals) + AVG(opponent_goals) FROM games”)”

echo -e “\nMost goals scored in a single game by one team:”

echo “$($PSQL “SELECT MAX(winner_goals) FROM games”)”

echo -e “\nNumber of games where the winning team scored more than two goals:”

echo “$($PSQL “SELECT COUNT(game_id) FROM games WHERE winner_goals > 2”)”

echo -e “\nWinner of the 2018 tournament team name:”

echo “$($PSQL “SELECT name FROM teams WHERE team_id = (SELECT winner_id FROM games WHERE round = ‘Final’ AND year = 2018)”)”

echo -e “\nList of teams who played in the 2014 ‘Eighth-Final’ round:”

echo “$($PSQL “SELECT name FROM games LEFT JOIN teams ON games.winner_id = teams.team_id WHERE round=‘Eighth-Final’ AND year=2014 UNION SELECT name FROM games LEFT JOIN teams ON games.opponent_id = teams.team_id WHERE round=‘Eighth-Final’ AND year=2014 ORDER BY name ASC”)”

echo -e “\nList of unique winning team names in the whole data set:”

echo “$($PSQL “SELECT DISTINCT name FROM games LEFT JOIN teams ON games.winner_id = teams.team_id ORDER BY name ASC”)”

echo -e “\nYear and team name of all the champions:”

echo “$($PSQL “SELECT year, name FROM games LEFT JOIN teams ON games.winner_id = teams.team_id WHERE round=‘Final’”)”

echo -e “\nList of teams that start with ‘Co’:”

echo “$($PSQL “SELECT name FROM teams WHERE Name LIKE ‘Co%’”)”

Your browser information:

User Agent is: Mozilla/5.0 (X11; Linux x86_64; rv:105.0) Gecko/20100101 Firefox/105.0

Challenge: World Cup Database - Build a World Cup Database

Link to the challenge:

It should be average of the sum of the goals…
(Add all goals in all columns then divide by total number of values added)

Exaclty. But the answer is something else. The answer in expected_output.txt is: 2.8125000000000000
Which is calculated by doing this: "SELECT AVG(winner_goals) + AVG(opponent_goals) FROM games"

But I think it should be done like so:

SELECT (AVG(winner_goals) + AVG(opponent_goals)) / 2 FROM games

or maybe like so

"(SELECT SUM(winner_goals) + SUM(opponent_goals)) / (2 * COUNT(winner_goals)) FROM games"

I probably overlooked something right :slight_smile:

Anyway: main question is, the output I produce seems the be the same as the one in expected_output.txt, so why it is not recognized as being correct?

I saw some people capture the output in a file and then they use the diff command to compare it against the expected result. Sometimes that helps reveal the difference.

thanks will try that!

Read this also

1 Like

thanks! Still does not do the trick though :smiley:

Using the diff command I get this here:

$ diff my_output.txt expected_output.txt 
60d59
< 2018|France
61a61
> 2018|France

that’s the only difference?
is there some extra hidden character there?

1 Like

omg… I think I got it. Its that France is before Germany

ORDER BY name DESC