SCCM Compliance Report for Chrome Using Report Builder 3.0

Question

I'm attempting to create a compliance report for Chrome and where it lists the collection, the number of devices with chrome installed (All Versions), and the number of devices with the latest version of Chrome I'm trying to specify for compliance. Once I have the two numbers I will create a column with a percentage of Chrome Compliance in the report builder. I can get the Collection with the Count of Total Machines with Chrome(Reguardless of Version) with the QUERY BELOW

select c.Name, 
Count(s.Name0) AS Installed
from V_R_System s
inner join v_GS_SoftwareFile vgs on vgs.ResourceId = s.ResourceId 
join v_FullCollectionMembership fcm on fcm.ResourceID = s.ResourceID
join v_Collection c on c.CollectionID = fcm.CollectionID
where vgs.FileName = 'Chrome.exe' AND c.Name like 
'%MyCollectionNamingScheme%'
Group By c.Name

The next step I need is to get the count with number of machines with Chrome Version 59. I know I can just edit the top query and get it but do I need to make the first number a variable? I'm new to SQL and I'm still learning. I thought about just making the queries seperate in the report builder but I don't know if that will give me an accurate answer by taking data from two seperate tables that use the same collections. I know I can do it with the QUERY BELOW to get me the number. I just want to know what the best way to go about getting this in a single query is or if I should do them seperate and combine them somehow in report builder.

select c.Name, 
Count(s.Name0) AS Installed
from V_R_System s
inner join v_GS_SoftwareFile vgs on vgs.ResourceId = s.ResourceId 
join v_FullCollectionMembership fcm on fcm.ResourceID = s.ResourceID
join v_Collection c on c.CollectionID = fcm.CollectionID
where vgs.FileName = 'Chrome.exe' AND vgs.FileVersion0 like '59%' AND c.Name 
like '%MyCollectionNamingScheme%'
Group By c.Name

Answer 1

Use a case statement combined with a Sum to count the entries with the version number.

SELECT      c.Name,
            COUNT( s.Name0 ) AS Installed,
            SUM( CASE
                   WHEN vgs.FileVersion0 LIKE (@FileVersion + '%')
                     THEN 1
                   ELSE 0
                 END
               ) AS CountCompliant
  FROM      V_R_System AS s
  INNER JOIN v_GS_SoftwareFile AS vgs
    ON vgs.ResourceId = s.ResourceId
  JOIN      v_FullCollectionMembership AS fcm
    ON fcm.ResourceID = s.ResourceID
  JOIN      v_Collection AS c
    ON c.CollectionID = fcm.CollectionID
  WHERE     vgs.FileName = 'Chrome.exe'
            AND c.Name LIKE '%MyCollectionNamingScheme%'
  GROUP BY  c.Name
;