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 |
Count records per year and month based on date & id
Using MySQL 8.0 window functions
SELECT *, COUNT(*) OVER(PARTITION BY location_id, type_id, YEAR(day_dt), MONTH(day_dt)) counts
FROM preliminar
ORDER BY day_dt
db<>fiddle
Select count for each day in a month
Since OP's date_column
is VARCHAR
type. We use STR_TO_DATE function:
SELECT DATE(STR_TO_DATE(date_column, "%m/%d/%Y %r")), COUNT(*)
FROM table
GROUP BY DATE(STR_TO_DATE(date_column, "%m/%d/%Y %r"));
Use DATE function, to convert a datetime expression to a date. Then use GROUP BY
to get COUNT datewise.
In case, you want to get data for a specific user (eg: CTC01) and datewise. You can do the following:
SELECT DATE(STR_TO_DATE(date_column, "%m/%d/%Y %r")), COUNT(*)
FROM table
WHERE username = 'CTC01'
GROUP BY DATE(STR_TO_DATE(date_column, "%m/%d/%Y %r"));
MYSQL query to show current month records count
You're close, COUNT()
doesn't only count TRUE
values, it counts any and all non-NULL
values, so it's counting the FALSE
values too.
Try...
SELECT
COUNT(*),
SUM(DATE_FORMAT(date, '%Y-%m-01') = DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01'))
FROM
orders
When summed, TRUE
is treated as 1
, and FALSE
is treated as 0
I also use DATE_FORMAT()
instead of MONTH()
so you don't accidentally count the values from the same month in previous years.
- If you want that, just change the
DATE_FORMAT()
back toMONTH()
and keep theSUM()
SQL : given a year and month how can I count the number of orders per WEEK
You can use CASE
to get a single week number then group by that number.
Use CROSS APPLY (VALUES
to avoid repeating code
SELECT
v.WeekNumber,
TotalOrders = COUNT(*)
FROM
dbo.Orders
CROSS APPLY (VALUES (
CASE WHEN DateCreated >= DATEFROMPARTS(@year, @month, 1) AND DateCreated < DATEFROMPARTS(@year, @month, 8)
THEN 1
WHEN DateCreated >= DATEFROMPARTS(@year, @month, 8) AND DateCreated < DATEFROMPARTS(@year, @month, 15)
THEN 2
WHEN DateCreated >= DATEFROMPARTS(@year, @month, 15)) AND DateCreated < DATEFROMPARTS(@year, @month, 22)
THEN 3
WHEN DateCreated >= DATEFROMPARTS(@year, @month, 22)) AND DateCreated < DATEFROMPARTS(@year, @month, 29)
THEN 4
ELSE 5
END
)) v(WeekNumber)
WHERE DateCreated >= DATEFROMPARTS(@year, @month, 1)
AND DateCreated < DATEADD(month, 1, DATEFROMPARTS(@year, @month, 1))
GROUP BY
v.WeekNumber;
Note the use of >= AND <
for a half-open interval. This ensure that the whole of the last day is included.
Count the number of records per month
As suggested in the comments, I replaced the temp table with a permanent table called 'calendar'.
CREATE TABLE `calendar` (
`date` date NOT NULL,
PRIMARY KEY (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
I filled this table with all dates from 2000-01-01 until 2100-12-31. I rewrote my query to this:
SELECT
COUNT(*),
c.date
FROM
calendar c
INNER JOIN table t
ON c.date BETWEEN t.start_date AND t.end_date
WHERE
DAYOFMONTH(c.date) = 1
AND
c.date BETWEEN '2016-01-01' AND '2016-12-31'
GROUP BY
c.date
Count records for every month in a year
SELECT COUNT(*)
FROM table_emp
WHERE YEAR(ARR_DATE) = '2012'
GROUP BY MONTH(ARR_DATE)
Related Topics
How to Subtract One Month from a Date Column
Er_Access_Denied_Error: Access Denied for User ''@'Localhost' (Using Password: No)
Query to Get All Those Names of Employees,Who Have 'A' as Their Middle Character in Their Name
Find a Matching Value in Date Range and Return Value
How to Display Half Records from Table - MySQL
How to Store Emoji Character in MySQL Database
Spring Data JPA Utf-8 Encoding Not Working
Pg Copy Error: Invalid Input Syntax for Integer
Difference Between "INNER JOIN" and "OUTER JOIN"
How to Merge Multiple Rows into Single in Oracle
How to Convert from Blob to Text in MySQL
Constraint for Phone Number in SQL Server
Using T-Sql, Return Nth Delimited Element from a String
Convert Varchar Mmddyyyy to Mm/Dd/Yyyy Datetime and Select the Most Recent Date Only
Combining Multiple Rows in SQL Server into One