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()

Answer 2

"select month(date),count(distinct ip) from visitors where date>='$y-01-01' and date<'$y-12-31' group by 1"

Answer 3

SELECT YEAR(date) YEAR,MONTHNAME(date) MONTH,count(id) TOTAL FROM table_name GROUP BY YEAR(date),MONTH(date)

THE QUERY IS LIKE THIS BECAUSE DIFFERENT PERSONS FROM SAME IP MAY VISIT