MYSQL COUNT per YEAR from DATE data

Question

i need to ask some question about multiple counts in mysql, so ihave this kind of data :

|  name  |  date                        | act  |
------------------------------------------------
| abc    |  2014-02-03 07:05:18         |  1   |
| abc    |  2014-02-03 08:05:18         |  1   |
| abc    |  2015-02-03 07:05:18         |  1   |
| ghi    |  2014-02-03 07:05:18         |  1   |
| ghi    |  2015-02-03 07:05:18         |  1   |
| klm    |  2014-02-03 07:05:18         |  1   |
| klm    |  2014-04-01 07:05:18         |  1   |

then I want to make a report like this :

|  name  |  count(2014)  |  count(2015)  |
------------------------------------------------
| abc    |       2       |      1        |
| ghi    |       1       |      1        |
| klm    |       2       |      0        |

How to make the count query?

Answer 1

If the years will be those two, or a limited set, you can do that with a conditional count

select  name,
        sum(case when year(date) = 2014 then 1 else 0 end) as count_14,
        sum(case when year(date) = 2015 then 1 else 0 end) as count_15
from    yourTable
group by name

Otherwise I would suggest moving the year to the rows and then changing it to column at presentation layer (or with a pivot)

select  name, year(date), count(*)
from    yourTable
group by name, year(date)