MySQL - multiple groups and multiple count

Question

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.

spencer7593 · Accepted Answer · 2017-06-08 16:02:46Z

1

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".)

answered Jun 8, 2017 at 16:02

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

slthomason Over a year ago

Worked great! Thanks a ton!

Collectives™ on Stack Overflow

MySQL - multiple groups and multiple count

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related