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

count, distinct, group-by, mysql, sql

Solution

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

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

Problem

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?

Original source

Related problems