How do i count total number of distinct elements in a column of MySQL table?

Question

i have a table named city in my database and I have tried using theselect distinct count(*) from city but it's not returning the answer that is required

Answer 1

You should apply DISTINCT inside COUNT() for the column that you want the result:

select count(distinct columnname) 
from city

Answer 2

Let's dissect the problem you are facing so that you can learn too rather than just copying out the solution.

You have to find out the distinct entries from a particular column in a table. You would use the DISTINCT clause. Refer

MySQL DISTINCT clause is used to remove duplicate records from the table and fetch only the unique records. The DISTINCT clause is only used with the SELECT statement.

Syntax:

SELECT DISTINCT expressions  
FROM tables  
[WHERE conditions]; 

Now you want to count the distinct entries from a column so you can just use the count clause with distinct clause enclosed in it.

For example -

select count(distinct columnname) 
from tablename