MySQL에서 현재 날짜에 등록된 사용자 수 세기 (COUNT + GROUP BY MONTH)

COUNT()와 GROUP BY MONTH(), CURDATE()를 조합해 오늘 날짜에 등록된 사용자 수를 월별로 집계합니다.

현재 날짜 확인

mysql> SELECT CURDATE();

+------------+
| CURDATE()  |
+------------+
| 2019-11-30 |
+------------+
1 row in set (0.00 sec)

테이블 생성 및 데이터 삽입

mysql> CREATE TABLE DemoTable1819 (
    -> Name VARCHAR(20),
    -> RegisteringDate DATETIME
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO DemoTable1819 VALUES('Chris','2019-11-29 12:30:34');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO DemoTable1819 VALUES('David','2019-11-30 12:30:34');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO DemoTable1819 VALUES('Mike','2019-12-30 12:30:34');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO DemoTable1819 VALUES('Sam','2018-11-30 11:00:00');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO DemoTable1819 VALUES('Carol','2019-11-30 10:00:00');
Query OK, 1 row affected (0.00 sec)

데이터 조회

mysql> SELECT * FROM DemoTable1819;

+-------+---------------------+
| Name  | RegisteringDate     |
+-------+---------------------+
| Chris | 2019-11-29 12:30:34 |
| David | 2019-11-30 12:30:34 |
| Mike  | 2019-12-30 12:30:34 |
| Sam   | 2018-11-30 11:00:00 |
| Carol | 2019-11-30 10:00:00 |
+-------+---------------------+
5 rows in set (0.00 sec)

오늘 날짜(2019-11-30) 등록자 수 월별 그룹핑

mysql> SELECT COUNT(RegisteringDate) AS Output
    -> FROM DemoTable1819
    -> WHERE DATE(RegisteringDate) = CURDATE()
    -> GROUP BY MONTH(RegisteringDate);

+--------+
| Output |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

해설: DATE(RegisteringDate)로 시간 떼고 날짜만 비교. 오늘(11-30) 데이터: David, Carol 2건. GROUP BY MONTH(...)로 월별 그룹핑(여기선 11월만 있음). COUNT(*) vs COUNT(컬럼): 후자는 NULL 제외.