Count and concatenate MySQL entries

Question

Essentially, I have a table that is like this:

FirstName, LastName, Type
Mark, Jones, A
Jim, Smith, B
Joseph, Miller, A
Jim, Smith, A
Jim, Smith, C
Mark, Jones, C

What I need to do is be able to display these out in PHP/HTML, like:

Name | Total Count Per Name | All Type(s) Per Name

which would look like...

• Mark Jones | 2 | A, C
• Jim Smith | 3 | B, A, C
• Joseph Miller | 1 | A
• Jim Smith | 3 | B, A, C
• Jim Smith | 3 | B, A, C
• Mark Jones | 2 | A, C

I have spent time trying to create a new table based off the initial one, adding these fields, as well as looking at group_concat, array_count_values, COUNT, and DISTINCT, along with other loop/array options, and cannot figure this out.

I've found a number of answers that count and concatenate, but the problem here is I need to display each row with the total count/concatenation on each, instead of shortening it.

Answer 1

How about doing it like this?

SELECT aggregated.* FROM table_name t
LEFT JOIN (
    SELECT
        CONCAT(FirstName, ' ', LastName) AS Name,
        COUNT(Type) AS `Total Count Per Name`,
        GROUP_CONCAT(Type SEPARATOR ',') AS `All Type(s) Per Name`
    FROM table_name
    GROUP BY Name) AS aggregated
ON CONCAT(t.FirstName, ' ', t.LastName) = aggregated.Name