SELECT COUNT(DISTINCT [name]) from several tables

Question

I can perform the following SQL Server selection of distinct (or non-repeating names) from a column in one table like so:

SELECT COUNT(DISTINCT [Name]) FROM [MyTable]

But what if I have more than one table (all these tables contain the name field called [Name]) and I need to know the count of non-repeating names in two or more tables.

If I run something like this:

SELECT COUNT(DISTINCT [Name]) FROM [MyTable1], [MyTable2], [MyTable3]

I get an error, "Ambiguous column name 'Name'".

PS. All three tables [MyTable1], [MyTable2], [MyTable3] are a product of a previous selection.

Bit

Do you want count from all three tables? or just from one of them? – Hamid

What do you mean by "non-repeating names"? – Kesterson

OK, say, MyTable1's [Name] column has values: "John", "Mary", "John", "Aaron". MyTable2's [Name] column has values: "Mary", "Aaron", "Aaron", "Mary". MyTable3's [Name] column has values: "John", "Mary", "John", "John". I need to get the count, such as: "John" = 5 times, "Mary" = 4 times, "Aaron" = 3 times. – Bit

Answer 1

After the clarification, use:

  SELECT x.name, COUNT(x.[name])
    FROM (SELECT [name]
            FROM [MyTable]
          UNION ALL
          SELECT [name]
            FROM [MyTable2]
          UNION ALL
          SELECT [name]
            FROM [MyTable3]) x
GROUP BY x.name

If I understand correctly, use:

  SELECT x.name, COUNT(DISTINCT x.[name])
    FROM (SELECT [name]
            FROM [MyTable]
          UNION ALL
          SELECT [name]
            FROM [MyTable2]
          UNION ALL
          SELECT [name]
            FROM [MyTable3]) x
GROUP BY x.name

UNION will remove duplicates; UNION ALL will not, and is faster for it.

Kesterson

I think you have covered all options now :) +1 – Obituary

And, the UNION ALL is the right way to go, no matter which solution is used :) – Trina

Guys, I apologize for misleading you. I'm just learning SQL. I'm also curious, will this code handle names in a case-sensitive matter, i.e. "John" and "john" count as 2, or as 1? – Bit

@Bit - case sensitivity is based on your server-level Collation settings. – Creativity

COUNT(DISTINCT ...) on the grouping key will always result in 1. – Boisterous

Answer 2

EDIT: Had to change after seeing recent comment.

Does this give you what you want? This gives a count for each person after combining the rows from all tables.

SELECT [NAME], COUNT(*) as TheCount
FROM
    (
     SELECT [Name] FROM [MyTable1]
     UNION ALL
     SELECT [Name] FROM [MyTable2]
     UNION ALL
     SELECT [Name] FROM [MyTable3]
     ) AS [TheNames]
GROUP BY [NAME]

Trina

Answer 3

Here's another way:

SELECT x.name, SUM(x.cnt)
FROM ( SELECT [name], COUNT(*) AS cnt
       FROM [MyTable]
       GROUP BY [name]
     UNION ALL
       SELECT [name], COUNT(*) AS cnt
       FROM [MyTable2]
       GROUP BY [name]
     UNION ALL
       SELECT [name], COUNT(*) AS cnt
       FROM [MyTable3]
       GROUP BY [name]
     ) AS x
GROUP BY x.name

Ironworks

Answer 4

In case you have different amounts of columns per table, like:

• table1 has 3 columns,
• table2 has 2 columns,
• table3 has 1 column

And you want to count the amount of distinct values of different column names, what it was useful to me in AthenaSQL was to use CROSS JOIN since your output would be only one row, it would be just 1 combination:

SELECT * FROM (
SELECT COUNT(DISTINCT name1) as amt_name1,
       COUNT(DISTINCT name2) as amt_name2,
       COUNT(DISTINCT name3) as amt_name3,
FROM table1 ) t1
CROSS JOIN
(SELECT COUNT(DISTINCT name4) as amt_name4,
        COUNT(DISTINCT name5) as amt_name5,
        MAX(t3.amt_name6) as amt_name6
 FROM table2
 CROSS JOIN
 (SELECT COUNT(DISTINCT name6) as amt_name6
  FROM table3) t3) t2

Would return a table with one row and their counts:

amt_name1 | amt_name2 | amt_name3 | amt_name4 | amt_name5 | amt_name6
    4123  |    675    |    564    |    2346   |   18667   |    74567

Groyne

Answer 5

A more recent solution, from PostgreSQL example, to get a grant total of all unique stock (in this case) names, wrapped up in a nice VIEW:

CREATE OR REPLACE VIEW data.view_count_distinct_symbols 
  AS
    SELECT COUNT(*) 
      FROM (SELECT DISTINCT symbol_name FROM data.d_bats_etf
          UNION ALL
            SELECT DISTINCT symbol_name FROM data.d_bats_stock
          UNION ALL
            SELECT DISTINCT symbol_name FROM data.d_nasdaq_etf
          UNION ALL
            SELECT DISTINCT symbol_name FROM data.d_nasdaq_stock
          UNION ALL
            SELECT DISTINCT symbol_name FROM data.d_nyse_arca_etf
          UNION ALL
            SELECT DISTINCT symbol_name FROM data.d_nyse_arca_stock
          UNION ALL
            SELECT DISTINCT symbol_name FROM data.d_nyse_etf
          UNION ALL
            SELECT DISTINCT symbol_name FROM data.d_nyse_mkt_etf
          UNION ALL
            SELECT DISTINCT symbol_name FROM data.d_nyse_mkt_stock
          UNION ALL
            SELECT DISTINCT symbol_name FROM data.d_nyse_stock);

...

    |count 
    |bigint
-----------
    | 28794

Jugglery