Monday, June 27, 2016

Union & Count Together in A SQL Query

I know it has been a while since I have given you a good tip...So without further ado.

Today I had a situation where I needed to give a count of items that were across a couple of tables and involved a few unions in SQL.  Since my query took up about 2 pages, I figured I would make a simple version for all you folks out there reading this.  I hope it helps.

For Example, let us say you have two tables ( Results & ArchivedResults) and were needing to get a count on them both.  Let us also assume you didn't use a JOIN because of the different criteria in each SQL statement.  Based off those results you need to get a count.  Below is how this can be accomplished.

Example Code:
SELECT PullResults.Name, COUNT(*) 
FROM  (SELECT Name FROM Results
UNION ALL
SELECT Name FROM ArchivedResults) as PullResults
GROUP BY Name
ORDER BY Name

If you like this post and want to see more, follow me on my website www.chadcompton.com
Or if you prefer...