Hello, I got stuck on the last requirement of the World Cup Database project even though my output looks just like the expected output. I’m not sure what is the correct way to share this but my final script is like this below.
Since it doesn’t specify which line or query is faulty I couldn’t solve it.
#! /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 SUM(winner_goals_avg) FROM
(SELECT AVG(games.winner_goals) AS winner_goals_avg FROM games
UNION
SELECT AVG(games.opponent_goals) FROM games) t
;")"
echo -e "\nMost goals scored in a single game by one team:"
echo "$($PSQL "SELECT MAX(winner_goals) FROM teams LEFT JOIN games ON team_id=winner_id;")"
echo -e "\nNumber of games where the winning team scored more than two goals:"
echo "$($PSQL "SELECT COUNT(*) FROM games WHERE winner_goals > 2")"
echo -e "\nWinner of the 2018 tournament team name:"
echo "$($PSQL "SELECT name FROM teams LEFT JOIN games ON team_id=winner_id WHERE year=2018 AND round = 'Final';")"
echo -e "\nList of teams who played in the 2014 'Eighth-Final' round:"
echo "$($PSQL " SELECT name FROM
(SELECT games.year, games.round, games.winner_id AS teams, games.winner_goals FROM games
UNION
SELECT games.year, games.round, games.opponent_id, games.opponent_goals FROM games) t
LEFT JOIN teams ON teams=team_id WHERE round='Eighth-Final' AND year=2014 ORDER BY name;
;")"
echo -e "\nList of unique winning team names in the whole data set:"
echo "$($PSQL "SELECT DISTINCT name FROM teams LEFT JOIN games ON team_id=winner_id WHERE winner_id IS NOT NULL ORDER BY name;")"
echo -e "\nYear and team name of all the champions:"
echo "$($PSQL "SELECT year, name FROM teams LEFT JOIN games ON team_id=winner_id WHERE round='Final' ORDER BY year ASC;")"
echo -e "\nList of teams that start with 'Co':"
echo "$($PSQL "SELECT name FROM teams WHERE name LIKE 'Co%';")"
And here is a side by side view of expected and actual outputs:
