Count occurrences of distinct values

I am trying to find a MySQL query that will display the number of occurrences of an ID value within a year.

Table:

a_id      year
----      ----
1        2010
1        2011
1        2012
1        2012
1        2013
1        2014
1        2015
2        2010
2        2011
2        2013
2        2014
2        2014
2        2015
3        2010
3        2010
3        2011

Expected output:

a_id    year    occurrences
----    -----   -----------
1       2010        1
1       2011        1
1       2012        2
1       2013        1
1       2014        1
1       2015        1
2       2010        1
2       2011        1
2       2013        1
2       2014        2
2       2015        1
3       2010        2
3       2011        1

I’m trying with the something along the lines of following sql query, but it gives me nothing like the expected output. It’s the 3rd column im struggling with.

SELECT a__id, year, count(distinct a_id) as occurrences
FROM table1
GROUP by year
ORDER by a_id

How can i create that 3rd column?

Answer

Scince you are grouping by a_id and year you of course get only 1 distinct value for group. Simply change count(distinct a_id) to count(*).

For example you get group:

1        2012
1        2012

Notice in this group distinct a_id values is 1. But you want count of all rows in group. With distinct you will get 1 as occurence in all groups.

EDIT:

Ok, I have missed that you are grouping only by year, so you should group by a_id also. The rest of the answer stays as is. So you end up with:

SELECT a__id, year, count(*) as occurrences
FROM table1
GROUP by a__id, year