MySQL: Returning total record count from a CROSS JOINed row?

Question

Here's my SQL Fiddle: http://www.sqlfiddle.com/#!2/672f4/1

I'm trying to get the total count of records for each name (technically, their cross-linked pid) that exists inside the table info. So, for example, the TOTAL column should read 3 for rob, and 1 for everyone else (jon, tim, and ben). The output should appear as so:

PID     NAME    TOTAL
1       rob     3
2       jon     1
3       tim     1
4       ben     1

STRUCTURE:

create table names (
  pid int(10),
  name char(20)
);

insert into names values
    (1, 'rob'),
    (2, 'jon'),
    (3, 'tim'),
    (4, 'ben');

create table info (
  id int(10),
  pid int(10),
  info char(20),
  extra char(10)
);

insert into info values
    (1, 1, 'rob blah', 'heh 1'),
    (2, 2, 'jon blah', 'ha 1'),
    (3, 3, 'tim blah', 'ho 1'),
    (4, 4, 'ben blah', 'hi 1'),
    (5, 1, 'rob blah', 'heh 2'),
    (6, 1, 'rob blah', 'heh 3');

QUERY:

SELECT pid, name, total FROM names CROSS JOIN ( SELECT COUNT(extra) AS total FROM info ) AS total;

Any assistance would be greatly appreciated.

Answer 1

SELECT names.pid, name, COUNT(extra) as total 
FROM names 
LEFT JOIN info 
ON info.pid=names.pid 
GROUP BY names.pid

fiddle