SQL to Find the Number of Distinct Values in a Column

SQL to find the number of distinct values in a column

You can use the DISTINCT keyword within the COUNT aggregate function:

SELECT COUNT(DISTINCT column_name) AS some_alias FROM table_name

This will count only the distinct values for that column.

SQL: count number of distinct values in every column

I appreciate all of the responses. I think the solution that will work best for me in this situation (counting the number of distinct values in each column of a table from an external program that has no knowledge of the table except its name) is as follows:

Run "describe table1" and pull out the column names from the result.

Loop through the column names and create the query to count the distinct values in each column. The query will look something like "select count(distinct columnA), count(distinct columnB), ... from table1".

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

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

How to get the count of each distinct value in a column?

SELECT
  category,
  COUNT(*) AS `num`
FROM
  posts
GROUP BY
  category

Count the number of distinct values of each row (SQL)

Split out each value into its own row (like it should have been stored in the first place), then union then up and (since union discards duplicates) just count the rows:

select id, description, count(*) unique_pays from (
    select id, description, nvl(pay1, -1) from mytable
    union select id, description, nvl(pay2, -1) from mytable
    union select id, description, nvl(pay3, -1) from mytable
    union select id, description, nvl(pay4, -1) from mytable
    union select id, description, nvl(pay5, -1) from mytable
    union select id, description, nvl(pay6, -1) from mytable
    union select id, description, nvl(pay7, -1) from mytable
    union select id, description, nvl(pay8, -1) from mytable
) x
group by id, description

I changed nulls into -1 so they would participate cleanly in the deduping.

---

Related Topics