SQL add columns of each record together

Question

To be blunt I don't know SQL however I don't want the answer, I want to work it out myself.

Here's the question:

Write a SQL query to calculate the number of goals for each team.

players

id name team_id goals
1 Joel 1 3
2 Ed 2 1
3 Simon 2 4

teams

id name
1 New Zealand
2 London

What I'm asking for is an arrow to information that will allow me to solve the question.

I've tried looking myself but I don't even know the correct terminology to ask the question, googling 'write sql to add fields for each row' just seems to return about adding columns or inserting.

Answer 1

You need to first try to JOIN your tables(id in Teams will be linked to TeamId in Players.) based on the foreign key columns.

Then you need to do the GROUP BY and use the aggregate function SUM to get the goals for each team.

So your query will be like:

select t.name, sum(p.goals) as cnt, 
from players p inner join teams t on p.teamid = t.id
group by t.name