3

I have a table structure like 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 a smart SELECT query, I want to fetch 3 columns that are:

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

I came up with my query below by the help of this solution in stackoverflow which gives me the first 2 columns that I need:

SELECT url, COUNT(*) AS total_ip FROM counter GROUP BY url ORDER BY total_ip DESC

But how can I add the 3rd column also into my query?

Can you please help me?

Community
  • 1
  • 1
Andre Chenier
  • 1,166
  • 2
  • 18
  • 37

1 Answers1

6

As you mentioned yourself, you can use DISTINCT keyword to get distinct URLs. Use COUNT to get count of IP and COUNT(DISTINCT ID) to get distinct IP for each URLs

Try this: 

SELECT DISTINCT URL AS D_URL
      ,COUNT(IP) AS IP
      ,COUNT(DISTINCT IP) AS D_IP
FROM counter
GROUP BY URL

Output:

╔═══════╦════╦══════╗
║ D_URL ║ IP ║ D_IP ║
╠═══════╬════╬══════╣
║ url1  ║  3 ║    2 ║
║ url2  ║  2 ║    2 ║
╚═══════╩════╩══════╝

See this SQLFiddle

Himanshu
  • 31,810
  • 31
  • 111
  • 133
  • 2
    Sometimes I wonder what's going through people's heads when the answer is almost verbatim what they asked in the question. "Count of distinct IP" => `COUNT(DISTINCT IP)` – Barmar May 02 '13 at 11:41
  • @Barmar - Yeah he almost describe everything what he want. – Himanshu May 02 '13 at 11:43