SELECT COUNT(DISTINCT [name]) from several tables
Solution 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.
Solution 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]
Solution 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
Solution 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
Related videos on Youtube
ahmd0
Updated on January 23, 2020Comments
-
ahmd0 over 4 years
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.
-
OMG Ponies almost 13 yearsWhat do you mean by "non-repeating names"?
-
-
Mikael Eriksson almost 13 yearsI think you have covered all options now :) +1
-
bobs almost 13 yearsAnd, the UNION ALL is the right way to go, no matter which solution is used :)
-
ahmd0 almost 13 yearsGuys, 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?
-
JNK almost 13 years@ahmd0 - case sensitivity is based on your server-level Collation settings.
-
sedavidw almost 13 years
COUNT(DISTINCT ...)
on the grouping key will always result in1
.