Count distinct value pairs in multiple columns in SQL

Question

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

Since you include an id, who is probably the primary key, all counts will add up to one... — Willem Van Onsem, Sep 10 '13 at 10:00
Maybe the "id" was a bad example, but the question was valid, and helped me find an answer. Have a +1 — Croo, Feb 26 '15 at 11:03

gvee · Accepted Answer · 2013-09-10T10:03:55.110

34

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

edited Sep 10 '13 at 10:03

answered Sep 10 '13 at 09:58

gvee

16,732
35
50

score 9 · Answer 2 · answered Sep 10 '13 at 09:57

9

Get all distinct id, name and address columns and count the resulting rows.

SELECT COUNT(*) FROM mytable GROUP BY id, name, address

answered Sep 10 '13 at 09:57

Dzhuneyt

8,437
14
64
118

2

...won't this return one row for each group? Perhaps I mis-interpreted the question... – gvee Sep 10 '13 at 10:02
No. `GROUP BY` simply groups multiple rows with the same value in the grouped column in a single row. So, if you have 100 rows with the same value for `name`, yes, it will return a single row, but if you have 100 rows with 10 different values for the `name` column (and you `GROUP BY name`), it will return 10 rows, each with a distinct `name`. – Dzhuneyt Sep 10 '13 at 10:10
This is flatly false – MaatDeamon Sep 10 '20 at 14:14
@Dzhuneyt, I don't think this is what the op was asking. I took it that he wanted the number of unique tuples, not counts of each unique tuple. – Tung Jan 29 '21 at 01:02

score 6 · Answer 3 · answered May 25 '17 at 14:18

6

Another (probably not production-ready or recommended) method I just came up with is to concat the values to a string and count this string distinctively:

SELECT count(DISTINCT concat(id, name, address)) FROM mytable;

answered May 25 '17 at 14:18

sk22

837
1
10
21

1

As written, this will only work id, name and address are all the same data types. If any of these are of a different data type, you will have to use the cast() or convert() function – Jim Lahman Jul 28 '20 at 15:00

score 2 · Answer 4 · answered Feb 19 '18 at 08:58

2

You can also do something like:

SELECT COUNT(DISTINCT id + name + address) FROM mytable

answered Feb 19 '18 at 08:58

Tadej

553
7
16

score 0 · Answer 5 · answered Jul 28 '20 at 15:06

Having to return the count of a unique Bill of Materials (BOM) where each BOM have multiple positions, I dd something like this:

select t_item, t_pono, count(distinct ltrim(rtrim(t_item)) + cast(t_pono as varchar(3))) as [BOM Pono Count]
from BOMMaster
where t_pono = 1
group by t_item, t_pono

Given t_pono is a smallint datatype and t_item is a varchar(16) datatype

Count distinct value pairs in multiple columns in SQL

5 Answers5

Linked