mysql로 월별 Group by
+---------------------+
| created_at |
+---------------------+
| 2010-01-21 00:00:00 |
| 2010-01-21 00:00:00 |
| 2010-01-21 00:00:00 |
| 2010-01-21 00:00:00 |
| 2010-01-21 00:00:00 |
| 2010-01-21 00:00:00 |
| 2010-01-21 00:00:00 |
| 2010-01-21 00:00:00 |
| 2010-01-21 00:00:00 |
| 2010-01-21 00:00:00 |
| 2010-01-21 00:00:00 |
| 2010-01-21 00:00:00 |
| 2010-04-06 00:00:00 |
| 2010-04-06 00:00:00 |
| 2010-04-13 00:00:00 |
| 2010-04-13 00:00:00 |
| 2010-04-14 00:00:00 |
| 2010-04-14 00:00:00 |
| 2010-04-14 00:00:00 |
+---------------------+
여기에 보면 이 데이터를 월별 묶으로 처리하고 싶은데요.
select created_at, count(*) AS cnt
from inquiries group by created_at
식으로 하면 데이터가
+---------------------+-----+
| created_at | cnt |
+---------------------+-----+
| 2010-01-21 00:00:00 | 12 |
| 2010-04-06 00:00:00 | 2 |
| 2010-04-13 00:00:00 | 2 |
| 2010-04-14 00:00:00 | 3 |
+---------------------+-----+
결과가 나옵니다.
위에서 보는것처럼 4월 한달을 한묶음으로 처리하고 싶은데요..
어떻게 해야하는지요?
잘 안됩니다.
http://dev.mysql.com/doc/refm
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_extract
요 함수를 써서 GROUP BY EXTRACT(YEAR_MONTH FROM created_at) 하면 될 것 같아요.
---8< 서명 -----------------
애니메이션 감상 기록 http://animeta.net/
글쓴이
select created_at, count(*) AS cnt, month(created_at) AS month
from inquiries GROUP BY EXTRACT(YEAR_MONTH FROM created_at)
하니 원하는 결과가 나왔습니다.
감사합니다~! -0-
댓글 달기