MYSQL select count of results by month

Question

I'm having a hard time searching for this answer, please let me know as it seems like something someone must have asked already.

I have a lot of rows in a db that look like this:

dest    at(timestamp)
A   2015-07-01
B   2015-07-01
A   2015-07-01
C   2015-07-01
A   2015-07-01

I want, for every month from 2015-07-01 until now, a counting of how many rows have which 'dest' so if these were the only rows, I would get:

2015-07-01
A 3
B 1
C 1

I can't seem to figure out how to do this. What do I need to do?

What you want to do is pivoting your table or not? If column `at(timestamp)` has various data, it must be a very difficult thing. Take a look of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — Blank, Apr 28 '16 at 01:22

score 0 · Accepted Answer · answered Apr 27 '16 at 22:13

0

Does this not work?

select dest, at(timestamp), count(*)
from _tablename_
group by dest, at(timestamp)

If you are trying to group by months, you can do

select dest, month(timestamp), count(*)
from _tablename_
group by dest, month(timestamp)

answered Apr 27 '16 at 22:13

matt

1 Answers1