Here is how I do it for OS and Service Packs

select Caption0, CSDVersion0, COUNT(1)

from  (

select distinct SerialNumber0,

(select top 1 Caption0 from dbo.v_GS_OPERATING_SYSTEM where

v_GS_OPERATING_SYSTEM.ResourceID = v_GS_PC_BIOS.ResourceID order by ResourceID desc) as Caption0,

(select top 1 CSDVersion0 from dbo.v_GS_OPERATING_SYSTEM where

v_GS_OPERATING_SYSTEM.ResourceID = v_GS_PC_BIOS.ResourceID order by ResourceID desc) as CSDVersion0

from v_GS_PC_BIOS

) A

group by Caption0, CSDVersion0

order by COUNT(1) desc

Compare output to out of the box SCCM report