[SOLVED] SELECT COUNT(DISTINCT [name]) from several tables

SELECT COUNT(DISTINCT [name]) from several tables

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.

Solution

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.