CODEWITHSUNDEEP
sql
Manu Chadha
Manu Chadha

Reputation: 16723

how to use count and distinct together

I have a table with names of countries. The country names are duplicated in the table. Eg say there are 8 rows in the table, 5 rows with country Germany and 3 with country UK. I want to get count the countries in the table (eg. I should get the number 2). But I am unable to come up with the query

I tried SELECT Country FROM Customers; but that will give me 8 rows. I tried SELECT DISTINCT Country FROM Customers; but that gives me 2 rows. I tried using count as SELECT DISTINCT Count(Country) FROM Customers; but I get 8 (probably because DISTINCT is applied on result set of SELECT Count(Country) FROM Customers; How could I get 2?

Upvotes: 0

Views: 182

Answers (4)

user330315
user330315

Reputation:

You can use distinct inside count:

select count(distinct country)
from customers;

Which is equivalent to:

select count(*)
from (  
  select distinct country
  from customers
  where country is not null
) t;

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Here is one way to do this using analytic functions:

SELECT ROW_NUMBER() OVER (ORDER BY COUNT(*)) cnt
FROM customers
GROUP BY country
ORDER BY cnt DESC
LIMIT 1;

Upvotes: 0

SMA
SMA

Reputation: 37023

You can use distinct country within count as below:

SELECT count(DISTINCT country)
FROM customers;

You can use distinct country within count and group by country for getting country name as well:

SELECT count(1), country
FROM customers
GROUP BY country;

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use inside distinct

SELECT count( distinct Country) FROM Customers

Upvotes: 0

Related Questions