MySQL Count Data for Last 7 Days

MySQL - How to get record count for each day of last 7 days?

I was waiting for you to post your own effort, but since somebody has already "jumped the gun" and started to post answers, so:

Assuming the name of the table is my_table, then try:

select date(message_datetime) as message_date, count(*) as cnt from my_table
where datediff(curdate(), date(message_datetime)) < 7
group by date(message_datetime)
order by date(message_datetime) desc

Update

Following the Strawberry's suggestion, here is an updated query that should be more performant if message_datetime is an indexed column:

select date(message_datetime) as message_date, count(*) as cnt from my_table
where message_date_time >= date_sub(curdate(), interval 6 day)
group by date(message_datetime)
order by date(message_datetime) desc

How to count the visits of the last 7 days in MySQL

you can use window function with range
check this out

select t.*, 
  sum(visit) over (partition by user order by `Date` 
    range between interval + 7 day preceding and current row  ) -1 as x
from t

Fiddle

Mysql return count for last 7 days

What about this one. It will return count of credits for a selected ID for each of the last 7 days.

SELECT (DATE(NOW()) - INTERVAL `day` DAY) AS `DayDate`, COUNT(`credits`) AS `credits`
FROM (
    SELECT 0 AS `day`
    UNION SELECT 1
    UNION SELECT 2
    UNION SELECT 3
    UNION SELECT 4
    UNION SELECT 5
    UNION SELECT 6
) AS `week`
LEFT JOIN `offer_process` ON DATE(`Date`) = (DATE(NOW()) - INTERVAL `day` DAY) AND `id` = 50
GROUP BY `DayDate`
ORDER BY `DayDate` ASC

Here is an example: http://sqlfiddle.com/#!9/3a6592/1

MySQL Count data for last 7 days

MySQL doesn't have recursive functionality, so you're left with using the NUMBERS table trick -

Create a table that only holds incrementing numbers - easy to do using an auto_increment:

DROP TABLE IF EXISTS `example`.`numbers`;
CREATE TABLE  `example`.`numbers` (
  `id` int(10) unsigned NOT NULL auto_increment,
   PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Populate the table using:
```
INSERT INTO NUMBERS
  (id)
VALUES
  (NULL)
```
...for as many values as you need.
Use DATE_ADD to construct a list of dates, increasing the days based on the NUMBERS.id value. Replace "2010-01-01" and "2010-01-02" with your respective start and end dates (but use the same format, YYYY-MM-DD HH:MM:SS). In this example, I subtracted the NUMBERS.id value from the CURRENT_DATE to get a list of sequential date values for the last week -
```
SELECT x.dt
  FROM (SELECT DATE_SUB(CURRENT_DATE, INTERVAL (n.id - 1) DAY) AS dt
          FROM numbers n
         WHERE n.id <= 7 ) x
```

LEFT JOIN onto your table of data based on the datetime portion.

   SELECT x.dt,
           COUNT(v.aid) AS num
     FROM (SELECT DATE_SUB(CURRENT_DATE, INTERVAL (n.id - 1) DAY) AS dt
             FROM numbers n
            WHERE n.id <= 7 ) x
LEFT JOIN VOTES v ON DATE(FROM_UNIXTIME(v.timestamp)) = DATE(x.dt)
 GROUP BY x.dt
 ORDER BY x.dt

Why Numbers, not Dates?

Simple - dates can be generated based on the number, like in the example I provided. It also means using a single table, vs say one per data type.

Previously:

  SELECT DATE(FROM_UNIXTIME(v.timestamp)) AS dt,
         COUNT(v.aid)
    FROM VOTES v
   WHERE DATE(FROM_UNIXTIME(v.timestamp)) BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
                                              AND CURRENT_DATE
GROUP BY DATE(FROM_UNIXTIME(v.timestamp))

How can I count entries for each day of the last 7 days in mysql?

If you want something like the following result :

date       |  num_texts
-------------------------
2015-10-28 | 3
2015-10-27 | 1
2015-10-26 | 8
etc.

then look at the query at MySQL group by date and convert from unix timestamp

However, if you would like the result to be something like :

days_ago   |  num_texts
-------------------------
0          | 6
1          | 3
2          | 1
3          | 8
etc.

then you can use the following query :

SELECT * 
FROM (SELECT DATEDIFF(now(), FROM_UNIXTIME(start_date)) AS days_ago, COUNT(id) AS num_texts 
      FROM USER_TEXT 
      GROUP BY DATE(FROM_UNIXTIME(start_date))) AS temp 
WHERE days_ago <= 7

Get last 7 days count even when there are no records

One solution is to create a calendar table containing all the dates you need. You can then left join it to your data to get what you are after

Count sum of sale from last 7 weekdays

One simple solution is to union on a load of zero records(only 7, not onerous) like:

SELECT d, sum(amount) 
FROM 
(
  SELECT dayofweek(date) as d, amount 
  FROM m1pay_dashboard.transactions 
  where date > (DATE(NOW()) - INTERVAL 7 DAY)
  UNION ALL
  SELECT 1, 0
  UNION ALL 
  SELECT 2, 0
  ...
  UNION ALL 
  SELECT 7, 0
) x
group by d

Being zero they won't participate in sums but provide a zero for those days that lack sales

If you want the day name in there too, add another column to the top query and a string to all the unions, but be mindful of any internationalization/localization obligations: it wouldn't be wise to hard code English words in if you will one day run the report on a French platform - better to leave it as a day number only and have the front end make it a name depending on the translation, even whether the target country considers day 1 a Sunday or a Monday