April 25, 2019 at 3:26 pm
I have 6 databases in SQL Server and each has a view "vTotalActives" which provides a count of the number of people called "Actives". I need a query that will join all those views and display the total from each. Is this possible?: e.g.:
Database1Actives = 552
Database2Actives = 728
Database3Actives = 125
April 25, 2019 at 3:33 pm
It is. You can use UNION ALL and then run the same count against each view.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 25, 2019 at 3:40 pm
The below works except it doesn't label each total:
SELECT [Actives] FROM [GCDF_DB].[dbo].[vTotalActives] as "Actives GCDF"
UNION ALL
SELECT [Actives] FROM [ACS_DB].[dbo].[vTotalActives] as "Actives ACS"
UNION ALL
SELECT [Actives] FROM [BCC_DB].[dbo].[vTotalActives] as "Actives BCC"
UNION ALL
SELECT [Actives] FROM [DCC_DB].[dbo].[vTotalActives] as "Actives DCC"
UNION ALL
SELECT [Actives] FROM [HSBCP_DB].[dbo].[vTotalActives] as "Actives HSBCP"
UNION ALL
SELECT [Actives] FROM [TMHP_DB].[dbo].[vTotalActives] as "Actives BC-TMH"
April 25, 2019 at 3:44 pm
You could label them as follows:
SELECT 'GCDF_DB', [Actives] FROM [GCDF_DB].[dbo].[vTotalActives] as "Actives GCDF"
UNION ALL
SELECT 'ACS_DB', [Actives] FROM [ACS_DB].[dbo].[vTotalActives] as "Actives ACS"
...etc
...etc
Thomas Rushton
blog: https://thelonedba.wordpress.com
April 25, 2019 at 3:49 pm
That works perfectly... thanks for the help!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply