Count All Records Per Day in a Specific Month

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

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 to MONTH() and keep the SUM()

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