CODEWITHSUNDEEP
mysql
user1810868
user1810868

Reputation: 1615

How to count distinct numbers on the table level and group them

I have the following table:

name | number
--------------
aaa  | 111
aaa  | 555
bbb  | 222
ccc  | 111
ddd  | 333
eee  | 444

and the following:

name | country
---------------
aaa  | US
bbb  | UK
ccc  | UK
ddd  | DE
eee  | UK

I need to select the number of distinct numbers (distinct on the whole table level), and group theses distinct values based on their countries.

I used the following query:

SELECT count(distinct t1.number), t2.country
FROM db.t1
JOIN db.t2
ON t1.name=t2.name
GROUP BY t2.country;

The result I get is:

3 | UK
2 | US
1 | DE

As shown in the first table, I have only 5 distinct numbers. But after the query, I get 6 distinct numbers. This is because the names: bbb, ccc, eee all belongs to UK, but ccc does not have a distinct number in the table level (duplicated with the number that is associated with name aaa, but aaa in another country).

The question is: How can I query distinct numbers on the table level (the current query counts distinct values but in the group level) grouped based on their countries?

Upvotes: 0

Views: 90

Answers (1)

Barmar
Barmar

Reputation: 781721

SELECT count(distinct t1.number), t2.country
FROM (SELECT number, name
      FROM db.t1
      GROUP BY number) t1
JOIN db.t2
ON t1.name=t2.name
GROUP BY t2.country;

This picks one of the countries with duplicate numbers arbitrarily.

FIDDLE

Upvotes: 1

Related Questions