Get mysql result and count specific field

Question

I have database result like this

ID | NAME     | TYPE
--------------------
1  | baseball | 1
2  | kickball | 1
3  | football | 1
4  | soccer   | 2

How do I do a select * so get all results but also get a total count of type = 2 in the results?

Any help appreciated.

Answer 1

Typically we manage to get this by the way of two distinct results set. However it is possible to get them all in one with a query similar to the following

   SELECT ID, Name, Type
    FROM MyTable
    UNION
    SELECT -1, Type, COUNT(*)
    FROM MyTable
    WHERE Type = 2
    GROUP BY Type
    ORDER BY ID

The assumption is that all normal IDs are > 0 allowing to the the -1 as a marker for the row with the count. This row will be first in the resultset, thanks to the ORDER BY.

Note that we could complicate things a bit and get a count for all types (or for several), by simply removing (or changing) the WHERE clause in the second query.

Answer 2

This will give you the type count for the current row's type in each row:

select t1.*, t2.TypeCount
from Table1 t1
inner join (
    select TYPE, count(*) as TypeCount
    from Table1
    group by TYPE
) t2 on t1.TYPE = t2.TYPE