Count distinct value pairs in multiple columns in SQL

sql count distinct
user1866731 picture user1866731 · Sep 10, 2013 · Viewed 103.8k times · Source

What query will count the number of rows, but distinct by three parameters?

Example:

Id      Name        Address 
==============================
1     MyName        MyAddress
2     MySecondName  Address2

Something like:

select count(distinct id,name,address) from mytable

Answer

gvee picture gvee · Sep 10, 2013

To get a count of the number of unique combinations of id, name and address:

SELECT Count(*)
FROM   (
        SELECT DISTINCT
               id
             , name
             , address
        FROM   your_table
       ) As distinctified

Related questions

GROUP BY and COUNT in PostgreSQL

The query: SELECT COUNT(*) as count_all, posts.id as post_id FROM posts INNER JOIN votes ON votes.post_id = posts.id GROUP BY posts.id; Returns n records in Postgresql: count_all | post_id -----------+--------- 1 | 6 3 | 4 3 | 5 3 | 1 1 | 9 1 | 10 (6 rows) I …

Read More
SQL distinct and count

I have a query where I want to get distinct dates, the phone numbers associated with those dates, and a count of the phone numbers per date. For example, I have a database with dates and phone numbers and I …

Read More
MySQL distinct count if conditions unique

I am trying to build a query that tells me how many distinct women and men there are in a given dataset. The person is identified by a number 'tel'. It is possible for the same 'tel' to appear multiple …

Read More