CODEWITHSUNDEEP
mysql
user15063
user15063

Reputation:

Counter number of items within each time period in mysql

I have a list of users with a registration timestamp assigned to each one of when they registered.

How can I count how many users registered in each month for my entire user list, which spans 23 months.

So the output should just be 2 columns, one with the month+year and the other how many users signed up in that month.

Can someone suggest a query to do that?

Example Data

INSERT INTO `users` (`id`, `regdate`) VALUES
(861490, 1314021552),
(944392, 1317760487),
(674263, 1311119415),
(287512, 1305242049),
(125134, 1299310950),
(714619, 1311658393),
(777858, 1312508020),
(812091, 1312918459),
(979515, 1319524143),
(1009713, 1321038952),
(395950, 1307314421),
(552213, 1309435427),
(549979, 1309395329),
(919872, 1316468483),
(385920, 1307196494),
(825096, 1313085971),
(967657, 1318961937),
(136631, 1299825660),
(838405, 1313315222),
(984647, 1319803409),
(239280, 1303860435),
(917747, 1316368790),
(621945, 1310480971),
(1078781, 1324529892),
(897934, 1315556339),
(311070, 1305899412),
(830274, 1313157183),
(518156, 1308952471),
(524182, 1309037939),
(524142, 1309037521);

Upvotes: 0

Views: 88

Answers (1)

piotrm
piotrm

Reputation: 12356

SELECT DATE_FORMAT(FROM_UNIXTIME(regdate), '%Y-%m') AS month, 
       COUNT(*) AS registrations 
FROM users 
GROUP BY month

Upvotes: 3

Related Questions