One of my clients had a SQL Server database with an email column repeated across multiple tables. He wanted two queries – all email’s that are not repeated across the table, i.e. distinct ones as well as a count of the email addresses across multiple tables in the database.
I gave him a query that will help him out to do a count across multiple tables as well as list all distinct emails. Let us consider the following sample data
![sql-distinct-count-multiple-tables sql-distinct-count-multiple-tables](http://lh3.ggpht.com/-9ciJRuOFz90/TiZpIrAWC0I/AAAAAAAACGI/pC_GlpCZhpQ/sql-distinct-count-multiple-tables%25255B6%25255D.png?imgmax=800)
Now to do a count of email across multiple tables, use the following query:
SELECT tt.email, COUNT(tt.email) as EmailCount
FROM ( SELECT email FROM @t
UNION ALL
SELECT email FROM @t1) tt
GROUP BY tt.email
OUTPUT
![image image](http://lh4.ggpht.com/-NLrRs9JlpUI/TiZpJ-XyW0I/AAAAAAAACGM/rXMXKy4ehGc/image%25255B2%25255D.png?imgmax=800)
Similarly to list all distinct email addresses, i.e the ones not repeated, use the HAVING clause (there are other ways too)
SELECT tt.email, COUNT(tt.email) as EmailCount
FROM ( SELECT email FROM @t
UNION ALL
SELECT email FROM @t1) tt
GROUP BY tt.email
HAVING COUNT(tt.email) = 1
![image image](http://lh4.ggpht.com/-g1tRXMzZi9k/TiZpKrZvExI/AAAAAAAACGQ/AkPhD5JVyxg/image%25255B12%25255D.png?imgmax=800)
No comments:
Post a Comment