Count of Multiple Application Software's vs Specific Collection Count and Percent.

Count of Application Software's vs Specific Collection Count

Below Query use full for Multiple application software's count and version vs Total active System Cont and Percent.


SELECT DisplayName0,
Version0,
Count(distinct arp.ResourceID) AS 'ApplicationCount',
 @CollID as CollectionID,
 tot.TotalClient,
 CONVERT(decimal(5,2),Count(distinct arp.ResourceID)*100.00/tot.TotalClient) As 'Percent'

  FROM 

( 
        SELECT   
               COUNT(1) TotalClient 
          FROM v_R_System sis 
               INNER JOIN v_RA_System_SMSAssignedSites sit 
                  ON sis.ResourceID = sit.ResourceID 
                 AND sis.Client0 = 1 
                 AND sis.Obsolete0 = 0 
                 AND sis.Active0 = 1 
           GROUP BY sit.SMS_Assigned_Sites0 
        )  AS tot,
v_Add_Remove_Programs arp

JOIN v_FullCollectionMembership fcm on arp.ResourceID=fcm.ResourceID

WHERE fcm.CollectionID = @CollID

and  DisplayName0 like 'xxxxxxx'
or  DisplayName0 like 'xxxxxxx'

GROUP BY DisplayName0,Version0,tot.TotalClient
ORDER BY  DisplayName0