I'm doing a practice set problem from CS50sql and can't seem to get the expected result.
Which teams might be the biggest competition for the A’s this year? In 6.sql, write a SQL query to return the top 5 teams, sorted by the total number of hits by players in 2001.
Call the column representing total hits by players in 2001 “total hits”. Sort by total hits, highest to lowest. Your query should return two columns, one for the teams’ names and one for their total hits in 2001.
My approach was to SUM all of the hits (H) then group by team_id. The JOIN was just to get the team name. Based on the expected output being 3-4x my output, I suspect that I'm incorrectly using the GROUP BY and SUM clause together, but everytime I look through the W3 syntax, it appears correct.
SELECT SUM(H) AS "total hits", teams.name
FROM performances
JOIN teams ON teams.id = performances.team_id
WHERE performances.year = 2001
GROUP BY teams.id
ORDER BY SUM(H) DESC
LIMIT 5;
MY OUTPUT | total hits | name | +------------+-------------------+ | 1663 | Colorado Rockies | | 1637 | Seattle Mariners | | 1566 | Texas Rangers | | 1559 | Cleveland Indians | | 1514 | Minnesota Twins | +------------+-------------------+
EXPECTED OUTPUT 618, Minnesota Twins 573, Colorado Rockies 507, Anaheim Angels 448, Seattle Mariners 365, Texas Rangers