mysql - Select Count of ip and Count of DISTINCT ip in same query -

i have table structure this:

table name : counter  id  |        datetime       |  url  | ip ------------------------------------------- 1   |2013-04-12 13:27:09    | url1  | ip01 2   |2013-04-13 10:55:43    | url2  | ip02 3   |2013-04-14 11:14:12    | url1  | ip03 4   |2013-04-15 23:23:55    | url2  | ip04 5   |2013-05-02 08:34:44    | url1  | ip03 

with smart select query, want fetch 3 columns are:

 distinct url    |    count of ip            |    count of distinct ip                 |    each distinct url  |    each distinct url   ------------------------------------------------------------------- url1            |    3                      |    2     url2            |    2                      |    2     

i came query below by of solution in stackoverflow gives me first 2 columns need:

select url, count(*) total_ip counter group url order total_ip desc 

but how can add 3rd column query?

can please me?

as mentioned yourself, can use distinct keyword distinct urls. use count count of ip , count(distinct id) distinct ip each urls

try this:

select distinct url d_url       ,count(ip) ip       ,count(distinct ip) d_ip counter group url 

output:

╔═══════╦════╦══════╗ ║ d_url ║ ip ║ d_ip ║ ╠═══════╬════╬══════╣ ║ url1  ║  3 ║    2 ║ ║ url2  ║  2 ║    2 ║ ╚═══════╩════╩══════╝ 

see sqlfiddle