Determining count of unique combination of two fields in mysql

Question

I have the following table:

Company        webdomain
-------        ---------
IBM            ibm.com
IBM            ibm.co.uk
IBM            ibm.in
CSC            csc.com
Infosys        infy.com
Intel          intel.com
Intel          intel.co.in

Question: how many companies have more than one webdomain?

How do I represent this as a SQL query?

I tried the following:

select count(distinct company, webdomain) 
from table 
where company = 'IBM';

This gave the number of web domains for IBM as 3, but however, when I want to create the same effect on finding out all companies with the following query:

select company, count(distinct company, webdomain) 
from table;

I get a single column, which is an empty value on company and some unrelated count.

I know that this will solve the problem:

select company, count(distinct company, webdomain) 
from table 
where company in (select distinct company from table);

But this last query takes way too long. Is there a better way to put it.

EDIT: Company, webdomain combination may not be unique. Ex: Two records with IBM, ibm.com.

Answer 1

you can just append "Group by" in your query to get desired result as:-

SELECT 
  `company`, `webdomain`, COUNT(DISTINCT `company`, `webdomain`) 
AS 
  `count_of_unique_combination` FROM `info` 
GROUP BY 
  `company`;

the output is:-

COMPANY     WEBDOMAIN   COUNT OF UNIQUE COMBINATION
CSC         csc.com     1
IBM         ibm.com     3
Infosys     infy.com    1
Intel       intel.com   2 

Answer 2

Use GROUP BY to get te desired result. Like:

select company, webdomain, count(*) from table group by company, webdomain;

Answer 3

Try with GROUP BY clause like

SELECT company,
       count(*)
FROM TABLE
GROUP BY company,
         webdomain;

Answer 4

As per Question: How many companies have more than one webdomain?

You may use the following query

select 
Company,
count(*) as num_subdomains
from 
your_table 
group by Company
having num_subdomains > 1