MySQL to Get The Count of Rows That Fall on a Date for Each Day of a Month

Getting a mysql count of rows for each of the last 30 days

If you just need count per day then use the following:

SELECT DATE_FORMAT(date, '%m/%d/%Y') AS Dates, count(*) as count 
FROM API_phones
WHERE date BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
Group by
Dates
ORDER BY Dates ASC

If you need it per day per extension you can try the following:

SELECT DATE_FORMAT(date, '%m/%d/%Y') AS Dates, extension, count(*) as count 
FROM API_phones
WHERE date BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
Group by
Dates, extension
ORDER BY Dates ASC

Get the count of rows for each month in specific date range

You cam filter the rows with the function DAY() and aggregate:

SELECT DATE_FORMAT(time, '%Y-%m') yearmonth,
COUNT(*) counter
FROM tablename
WHERE DAY(time) <= DAY(CURRENT_DATE)
GROUP BY yearmonth

See the demo.

Results:























yearmonthcounter
2005-072
2005-082
2006-093

MySQL Counting Active Records Each Day in Date Range

See this question for info on generating date ranges; the same approach should work for you. I can't test at the moment, but it would look something like:

SET @date_min = '2017-01-01';
SET @date_max = '2017-01-31';

SELECT
dg.date,
COUNT(pb.id) as daily_count
from (
select DATE_ADD(@date_min, INTERVAL (@i:=@i+1)-1 DAY) as `date`
from information_schema.columns,(SELECT @i:=0) gen_sub
where DATE_ADD(@date_min,INTERVAL @i DAY) BETWEEN @date_min AND @date_max
) date_generator dg
left join products_booking pb on (pb.released IS NULL OR pb.released >= dg.date) AND (pb.arrived <= dg.date)
GROUP BY dg.date
ORDER BY dg.date
;

MySQL Query GROUP BY day / month / year

GROUP BY YEAR(record_date), MONTH(record_date)

Check out the date and time functions in MySQL.

How to query months and number of days in a month of current year in SQL?

You can do this with a query:

select monthname(makedate(year(curdate()), 1) + interval (x.mon - 1) month) as month_name,
day(last_day(makedate(year(curdate()), 1) + interval (x.mon - 1) month)) as days_in_month
from (select 1 as mon union all select 2 union all select 3 union all select 4 union all
select 5 as mon union all select 6 union all select 7 union all select 8 union all
select 9 as mon union all select 10 union all select 11 union all select 12
) x
order by x.mon;

Here is a db<>fiddle.

MYSQL select count of rows that fall in a month for every month of the year

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

  1. 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;
  2. Populate the table using:

    INSERT INTO NUMBERS
    (id)
    VALUES
    (NULL)

    ...for as many values as you need.

  3. Use DATE_ADD to construct a list of times, increasing the months based on the NUMBERS.id value:

    SELECT x.*
    FROM (SELECT DATE_FORMAT(DATE_ADD('2010-01-01', INTERVAL n.id - 1 MONTH), '%Y-%m-%d')
    FROM numbers n) x
  4. LEFT JOIN onto your table of data based on the time portion:

       SELECT x.ts AS timestamp,
    SUM(CASE WHEN x.ts BETWEEN y.date_from AND y.date_to THEN 1 ELSE 0 END) AS cnt
    FROM (SELECT DATE_FORMAT(DATE_ADD('2010-01-01', INTERVAL n.id - 1 MONTH), '%Y-%m-%d') AS ts
    FROM numbers n) x
    LEFT JOIN TABLE y ON x.ts BETWEEN y.date_from AND y.date_to
    GROUP BY x.ts


Related Topics



Leave a reply



Submit