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:
yearmonth | counter |
---|---|
2005-07 | 2 |
2005-08 | 2 |
2006-09 | 3 |
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 -
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 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) xLEFT 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
Grouping by Date, Return Row Even If No Records Found
Delete ... from ... Where ... In
"Pivoting" a Table in SQL (I.E. Cross Tabulation/Crosstabulation)
How to Find Tables Which Reference a Particular Row via a Foreign Key
How to Count Most Consecutive Occurrences of a Value in a Column in SQL Server
Why Google's Bigtable Referred as a Nosql Database
Sql Server Management Studio 2008 Scheduled Export to Ms Access
Order by Column1 If Column1 Is Not Null, Otherwise Order by Column2
Sql - Select Max() and Accompanying Field
Trying to Connect to an Odbc Server Using Rodbc in Ubuntu
Apex 5.0: Show a Progress Bar While Database Action Is Performed
Writing a Recursive SQL Query on a Self-Referencing Table
Sqlserver - How to Find Dependent Tables on My Table
Attaching an Mdf File Without Ldf File
Left Join with Dynamic Table Name Derived from Column
How to Analyze 'Dbcc Memorystatus' Result in SQL Server 2008