Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 493
ahmd0 Avatar asked Jul 15 '11 19:07

ahmd0


People also ask

How do I count multiple distinct values in SQL?

To count the number of different values that are stored in a given column, you simply need to designate the column you pass in to the COUNT function as DISTINCT . When given a column, COUNT returns the number of values in that column. Combining this with DISTINCT returns only the number of unique (and non-NULL) values.

How do you do a distinct count in select?

The correct syntax for using COUNT(DISTINCT) is: SELECT COUNT(DISTINCT Column1) FROM Table; The distinct count will be based off the column in parenthesis. The result set should only be one row, an integer/number of the column you're counting distinct values of.

Can count and distinct be used together?

Yes, you can use COUNT() and DISTINCT together to display the count of only distinct rows. SELECT COUNT(DISTINCT yourColumnName) AS anyVariableName FROM yourTableName; To understand the above syntax, let us create a table.

Can you use select distinct and GROUP BY?

Well, GROUP BY and DISTINCT have their own use. GROUP BY cannot replace DISTINCT in some situations and DISTINCT cannot take place of GROUP BY. It is as per your choice and situation how you are optimizing both of them and choosing where to use GROUP BY and DISTINCT.


1 Answers

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.

like image 87
OMG Ponies Avatar answered Oct 25 '22 00:10

OMG Ponies