JapanRob JapanRob - 8 days ago 5
MySQL Question

How to use group by, count, and where in MYSQL

I have a table, described like so:

id (int), link (varchar512), text (varchar80), status (varchar10), created (timestamp), updated (timestamp), user (varchar)


What I need to do is get the total count of rows per user between two timestamps.

So, for example, let's say I want to get the total number of rows for users in the database. That is just a simple

SELECT user, COUNT(*) FROM table_name GROUP BY user;


If I want to get all the rows, for say October, I can do:

SELECT * FROM table_name WHERE created > "2016-10-01 00:00:00" and created < "2016-11-31 23:59:59"


My problem, is I cannot combine the two. I try, and I get syntax errors. I think that I need to run the where query, and then do a count based on that, but I'm not sure how do to that.

Answer

Hope this helps.

SELECT user, count(*) 
FROM table_name
WHERE created > "2016-10-01 00:00:00" and created < "2016-11-31 23:59:59"
GROUP BY user;
Comments