CODEWITHSUNDEEP
mysqlsqldatetime
Moritz
Moritz

Reputation: 153

Select entries by day over the last 7 days

I want to get the count of the registered users in the past 7 days, grouped. 

+-----+------------+--------------+
| id  | username   | created      |
+-----+------------+--------------+
|  1  | Vlad       | 1360168194   |
+-----+------------+--------------+
|  2  | Test       | 1360168194   |
+-----+------------+--------------+

This is my table. I want to have 7 rows of results with the date of the day, and count(id) as the result for the users that registered.

I tried different solutions and none of them really fitted my needings. Are there any ideas?

Upvotes: 0

Views: 112

Answers (1)

John Woo
John Woo

Reputation: 263723

SELECT  DATE(FROM_UNIXTIME(columName)), COUNT(ID) totalCOunt
FROM    tableName
WHERE   DATE(FROM_UNIXTIME(columName)) BETWEEN CURDATE() + INTERVAL -7 DAY AND CURDATE()
GROUP   BY DATE(FROM_UNIXTIME(columName))

Other Source(s)

Upvotes: 2

Related Questions