mysql query for website visiter count

Question

I have a table to store website visiter count bellow

id   date          ip            
-----------------------------
1    1-1-2012     195.165.1.2
2    1-1-2012     195.165.1.3
3    12-2-2012    195.165.1.8

and i want a mysql query for get count of each month of current year

Answer 1

seeing your DATE column, i assumed that you save dates as VARCHAR

SELECT  MONTHNAME(STR_TO_DATE(date, '%c-%e-%Y')) `monthName`, COUNT(*) totalCount
FROM    tableName
WHERE   YEAR(STR_TO_DATE(date, '%c-%e-%Y')) = YEAR(CURDATE())
GROUP BY  MONTHNAME(STR_TO_DATE(date, '%c-%e-%Y'))

• SQLFiddle Demo

OTHER SOURCEs

• STR_TO_DATE()
• YEAR()
• CURDATE()
• MONTHNAME()