MySQL query to sum one column and count another column, from two tables, based on a common value?

I’m having issues getting my head around a query that I am trying to build. I’m hoping for a bit of help here…

I have two tables. Each table has two columns. They are described as follows:

Table: “Token”

 Column 1: "Name" varchar(20)
 Column 2: "Color" varchar(10)

Table: “Score”

 Column 1: "Name" varchar(20)
 Column 2: "Points" Int

I want a query that will list each name once, and with each name the sum of Points for that name, and count of where Color=”RED”

Sample data for table 1:

"BOB","GREEN"
"LARRY","RED"
"JIM","BLUE"
"JIM","RED"
"FRANK","RED"
"BOB","BLUE"
"JIM","RED"

Sample data for table 2:

"LARRY",100
"BOB",40
"JIM",200
"BOB",100
"PAUL",250

My Table output would be similar to (row order isn’t important):

BOB,140,0
LARRY,100,1
JIM,200,2
FRANK,0,1
PAUL,250,0

How can I accomplish this?

Answer

You can aggregate, then join:

select t1.name, t1.reds, t2.points
from (select name, sum(color = 'RED') reds from table1 group by name) t1
inner join (select name, sum(points) points from table2 group by name) t2 
    on t1.name = t2.name

If names are missing in either table, then you can use union all instead:

select name, sum(reds) reds, sum(points) points
from 
    select name, 1 reds, 0 points from table1 where color = 'RED'
    union all
    select name, 0, points points from table2
) t 
group by name