CODEWITHSUNDEEP
mysql
slthomason
slthomason

Reputation: 383

MySQL - multiple groups and multiple count

I have a table like such named urlTraffic:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | bigint       | NO   | MUL | NULL    |                |
| location   | char(17)     | NO   |     | NULL    |                |
| domain     | varchar(128) | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

I need to get the count of the top 250 domains visited, and then find a count of the total number of locations that it was visited at.

This gives me the top 250 most commonly visited domains:

 select count(1) as domain_visits, domain 
 from urlTraffic 
 group by domain 
 order by domain_visits DESC 
 LIMIT 250;

with results like: 

+---------------+-----------------+
| domain_visits |     domain      |
+---------------+-----------------+
|    183002     | google.com      |
|    150181     | facebook.com    |

and I know that this gives me the a list of the locations:

 select distinct(location) 
 from urlTraffic 
 where domain = 'google.com';

However, I need the result set to be like this:

+---------------+-------------------------------------+
| domain_visits |     domain      |  number_of_sites  |
+---------------+-------------------------------------+
|    183002     | google.com      |          15       |
|    150181     | facebook.com    |          17       |
|    100002     | t.lkqd.net      |          4        |

I know there is a way to perform this query, but can't figure it out.

Upvotes: 0

Views: 29

Answers (1)

spencer7593
spencer7593

Reputation: 108370

Something like this will return the specified result

  SELECT COUNT(1) AS domain_visits
       , ut.domain
       , COUNT(DISTINCT ut.location) AS number_of_sites
    FROM urlTraffic ut 
   GROUP BY ut.domain
   ORDER BY domain_visits DESC
   LIMIT 250

(if I understood the specification. It's a bit confusing in that the result of this first query is shown with column named "site", when the first query is returning a column named "domain". This answer is based on the assumption that references to "site" were actually intended to be references to "domain".)

Upvotes: 1

Related Questions