SELECT COUNT (DISTINCT [name]) from several tables

Question

I can perform the following SQL Server selection of individual (or non-duplicate names) from a column in a single table as follows:

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

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

If I ran something like this:

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

I get the error message: "Ambiguous column name" Name ".

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

Answer 1

After 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 be faster for him.

Answer 2

EDIT: Changing the last comment changed.

Does it give you what you want? This gives an account for each person after combining 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] 

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