Friday 13 June 2014

How to get Distinct Count across Multiple Tables in SQL Server

Hi Friends,in this article I will explain about  How to get Distinct Count across Multiple Tables in SQL Server.
I already explained in the previous articles about How to Remove/Delete duplicate records/Rows from a table in SQL Server,SQL Server 2008 : How to Remove Special Characters in a String , STUFF and PATINDEX Examples and How to alter primary key column for existing table in sql server 2008
Before knowing How to get Distinct Count across Multiple Tables first know about UNION ALL Operator.

UNION ALL OPERATOR:The SQL UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It returns all rows from the query (even if the row exists in more than one of the SELECT statements).

Each SELECT statement within the UNION ALL must have the same number of fields in the result sets with similar data types.

To get Distinct Count across Multiple Tables in SQL Server i took two temp tables as shown in the below.


Distinct Count across Multiple Tables in SQL Server:

DECLARE @TABLE1 TABLE (
  Fname VARCHAR(50),
  lastname VARCHAR(50)
)
INSERT INTO @TABLE1
  VALUES ('Kishore', 'P')
INSERT INTO @TABLE1
  VALUES ('SaiR', 'P')
INSERT INTO @TABLE1
  VALUES ('Krish', 'S')
DECLARE @TABLE2 TABLE (
  id INT,
  Fname VARCHAR(50)
)
INSERT INTO @TABLE2
  VALUES (1, 'Kishore')
INSERT INTO @TABLE2
  VALUES (2, 'SaiR')
INSERT INTO @TABLE2
  VALUES (3, 'Anil')
SELECT
  nt.Fname,
  COUNT(nt.Fname) AS NameCount
FROM (SELECT
  Fname
FROM @TABLE1
UNION ALL
SELECT
  Fname
FROM @TABLE2) nt

GROUP BY nt.Fname

In the above query I do UNION ALL two tables to get unique data.

The output of above query as shown in the below figure.

2 comments:

© 2012-2018 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.