Question

Answer the following question it is solved using SQL and Lahmans baseball database it uses the 2 tables below.

salaries

yearID int

teamID text

IgID text

playerID text

salary int

teams

yearID int

IgID text

teamId text

franchID text

divID text

Rank int

G int

Ghome text

W int

L int

DivWin text

WCWin text

LgWin text

WSWin text

R int

AB int

H int

2B int

3B int

Hr int

BB int

SO int

SB int

CS text

HBP text

SF text

RA int

ER int

ERA double

CG int

SHO int

SV int

IPouts int

HA int

HRA int

BBA int

SOA int

E int

DP text

FP double

name text

park text

attendance text

BPF int

PPF int

teamIDBR text

teadIDahman45 text

teamIDretro

playerID text

salary int

1. Which team spent the most money on player salaries in 2010?

   SELECT s.teamID, s.yearID, SUM(salary)

   FROM salaries s

   WHERE s.yearID = '2010'

   GROUP By s.teamID

   ORDER BY SUM(salary) desc; Running this query I get the result NYA 206,333,389 this number is correct. When I join the tables so I can get the right team name like this: SELECT s.teamID, s.yearID, SUM(salary), name
   FROM salaries s JOIN teams t ON (t.teamID = s.teamID)
   WHERE s.yearID = '2010'
   GROUP By s.teamID
   ORDER BY SUM(salary) desc; I get the result NYA 2010 21665005845 New York Highlanders

   
   What needs to be changed so I get just the salaries for the 2010 season rather than the all time salary for teams, and that I get the current NYA team name?

Answer 1

Greetings!!

You can use SUM(s.salary) , as in this way you can get salaries for the 2010 season rather than the all time salary for teams.

So the query will be:

SELECT s.teamID, s.yearID, SUM(s.salary), name
FROM salaries s JOIN teams t ON (t.teamID = s.teamID)
WHERE s.yearID = '2010'
GROUP By s.teamID
ORDER BY SUM(s.salary) desc;

***********************************************************************************************************************************

**you can ask any doubts in comments and if you like the solution then please give it a thumbs up :)