Hi Ronan, I don't know if i understand your need, but your query gives something like that : mysql> SELECT year, month, day, ip, COUNT(*) AS access -> FROM access -> WHERE year = 2005 -> AND month = 5 -> GROUP BY year, month, day, ip -> ORDER BY year, month, day; +------+-------+------+-------------+--------+ | year | month | day | ip | access | +------+-------+------+-------------+--------+ | 2005 | 5 | 13 | 192.168.0.1 | 2 | | 2005 | 5 | 13 | 192.168.0.2 | 1 | | 2005 | 5 | 14 | 192.168.0.2 | 1 | | 2005 | 5 | 15 | 192.168.0.3 | 1 | +------+-------+------+-------------+--------+ 4 rows in set (0.00 sec)
Your last email lets me understand that your want this data + IP. I tought to group_concat : mysql> SELECT year, month, day, group_concat(ip),count(*) AS access -> FROM access -> WHERE year = 2005 -> AND month = 5 -> GROUP BY year, month,day -> ORDER BY year, month, day; +------+-------+------+-------------------------------------+--------+ | year | month | day | group_concat(ip) | access | +------+-------+------+-------------------------------------+--------+ | 2005 | 5 | 13 | 192.168.0.1,192.168.0.1,192.168.0.2 | 3 | | 2005 | 5 | 14 | 192.168.0.2 | 1 | | 2005 | 5 | 15 | 192.168.0.3 | 1 | +------+-------+------+-------------------------------------+--------+ 3 rows in set (0.00 sec) To drop multiple IP, you can use distinct : mysql> SELECT year, month, day, group_concat(distinct ip),count(*) AS access -> FROM access -> WHERE year = 2005 -> AND month = 5 -> GROUP BY year, month,day -> ORDER BY year, month, day; +------+-------+------+---------------------------+--------+ | year | month | day | group_concat(distinct ip) | access | +------+-------+------+---------------------------+--------+ | 2005 | 5 | 13 | 192.168.0.1,192.168.0.2 | 3 | | 2005 | 5 | 14 | 192.168.0.2 | 1 | | 2005 | 5 | 15 | 192.168.0.3 | 1 | +------+-------+------+---------------------------+--------+ 3 rows in set (0.00 sec) But when you group by year-month-day-ip you have distinct year-month-day-ip as you said. The problem is that the count(*) is for those distinct values. I hope that this is near what you need. Mathias Selon Ronan Lucio <[EMAIL PROTECTED]>: > Mathias, > > > Hi, > > look at group by ... with rollup at : > > http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html > > Thank you very much for your help. > > My needs arenīt this, exactly. > > GROUP BY WITH ROLLUP, returns me several lines of the > same day (one per IP), plus the total. > > I need that every year-month-day-ip be counted as 1. And I > need this total per day. > > Thank you, > Ronan > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]