CODEWITHSUNDEEP
mysql
Moskau Peterson
Moskau Peterson

Reputation: 95

Read user registration with count per day

I have a table with user data and I would like to do an efficient query to SELECT the user Count (userId e.g.) PER DAY.

So the output will be:

20/07/2015 - 50 new users
21/07/2015 - 23 new users
22/07/2015 - 19 new users

How can I do it with one query? A while() loop and then doing a query per day each seems very inefficient for me.

Upvotes: 2

Views: 540

Answers (1)

Abhishek Bhardwaj
Abhishek Bhardwaj

Reputation: 242

You could have a schema which has the following structure(I'm using MySql's syntax):

create table users(
  user_id int,
  create_date date
);

Say it's populated with following data:

insert into users values (1, STR_TO_DATE('1-01-2012', '%d-%m-%Y'));
insert into users values (2, STR_TO_DATE('1-01-2012', '%d-%m-%Y'));
insert into users values (3, STR_TO_DATE('1-01-2012', '%d-%m-%Y'));
insert into users values (4, STR_TO_DATE('2-01-2012', '%d-%m-%Y'));

If you select the user id's and group them by create_date, you'll have the required response using single query:

 select count(user_id) as count,create_date  from users group by create_date;

count   create_date
3       January, 01 2012 00:00:00
1       January, 02 2012 00:00:00

Upvotes: 1

Related Questions