1

I'm trying to get a count from last week visits.

Up to now, i have this working:

SELECT FROM_UNIXTIME(login),COUNT(*)
FROM users
WHERE FROM_UNIXTIME(login) >= (CURDATE() - INTERVAL DAYOFWEEK(CURDATE())+6 DAY)
GROUP BY DAYOFWEEK(FROM_UNIXTIME(login));

Results:

+----------------------+----------+
| FROM_UNIXTIME(login) | COUNT(*) |
+----------------------+----------+
| 2013-04-08 12:49:04  |        1 |
| 2013-04-10 17:29:21  |        2 |
| 2013-04-05 21:27:00  |        1 |
+----------------------+----------+

Problems:

-Table is not ordered by date;

-I'd like to show all 7 rows, even if count value='0'.

How can i fix this? Thanks in advance!

[UPDATE]

Order by date solved with:

ORDER BY FROM_UNIXTIME(login);

Just need to show all 7 rows of week!

David R
  • 429
  • 1
  • 7
  • 15
  • I don't think it is possible to get a row if count is 0, or at least not with much complicated query (if even possible). Count is the result of the select, and if there's no record for a specific date, there's simply no record to display a count for – Laurent S. Apr 10 '13 at 16:54
  • You need to use `order by` and `limit` clause – TNK Apr 10 '13 at 16:55
  • @TNK, thanks! order by is working now. Only need to figure out how to show all 7 rows of entire week... – David R Apr 10 '13 at 16:59

1 Answers1

0

You can use the query from this answer which can generate a list of dates, and then left join your query against it:

generate days from date range

Community
  • 1
  • 1
D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283