Select sub.Name,
sub.Number
From (Select Top 1000000 
'Assets with Errors' as [Name],
count(tblAssets.AssetID) as Number
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join (Select Distinct Top 1000000 tblErrors.AssetID As ID,
Max(tblErrors.Teller) As ErrorID
From tblErrors
Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID =
ScanningError.ID
Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
Where tblState.Statename = 'Active' and (tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '')
UNION
Select Top 1000000 
'Successful Scans' as [Name],
count(tblAssets.AssetID) as Number
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join (Select Distinct Top 1000000 tblErrors.AssetID As ID,
Max(tblErrors.Teller) As ErrorID
From tblErrors
Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID =
ScanningError.ID
Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
Where tblState.Statename = 'Active' and (tblErrors.ErrorText Is Null Or
tblErrors.ErrorText = '')) as sub