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]
