VID	SERVERIP	BASESCORE	EXPLOITABILITY	SCANCOMPLETE
51	192.168.0.1	5		H                 some date
31	192.168.0.1	9		H                 some date
42	192.168.0.2	3		POC             some date
64	192.168.0.2	3		H                 some date
85	192.168.0.3	5		POC             some date
26	192.168.0.4	9		H                 some date
37	192.168.0.4	9		POC             some date

SERVERIP 	[COUNT OF CRIT VIDs]
192.168.0.1	1
192.168.0.2	0
192.168.0.3	0
192.168.0.4	2

WITH SUMMARY AS(
SELECT SERVERIP, VID, BASESCORE, exploitability, scancomplete, 
ROW_NUMBER() OVER (PARTITION BY SERVERIP, VID ORDER BY SCANCOMPLETE DESC) AS RANK
 FROM VID_SERVER 
WHERE 
BASESCOREVALUE IS NOT NULL
and BASESCORE >= 9
and exploitabiltiy in ('H','POC','F')
)

  SELECT SERVERIP, [BASESCORE], SCANCOMPLETE, COUNT(*) AS [COUNT]
     FROM         SUMMARY x
     WHERE     x.rank = 1 AND x.SCANCOMPLETE =
                                (SELECT     MAX(SCANCOMPLETE)
                                  FROM          VID_SERVER C
                                  WHERE      x.[SERVERIP] = C.SERVERIP)
     GROUP BY SERVERIP, [BASESCORE], SCANCOMPLETE
     ORDER BY [SERVERIP] DESC

if object_id('tempdb..#t1') is not null
drop table #t1

create table #t1 ( SERVERIP varchar(20), BASESCORE int)

insert into #t1 (BASESCORE,SERVERIP)
		values    (5,'192.168.0.1')
				, (9,'192.168.0.1')
				, (3,'192.168.0.2')
				, (3,'192.168.0.2')
				, (5,'192.168.0.3')
				, (9,'192.168.0.4')
				, (9,'192.168.0.4')
				
select SERVERIP
       , SUM( CASE WHEN BASESCORE >= 9 THEN 1 ELSE 0 END) CT
from  #t1
GROUP BY SERVERIP		

DECLARE @Example TABLE
(
    ServerIP    varchar(20) NOT NULL,
    VID         integer NOT NULL
);

INSERT @Example
    (ServerIP, VID)
VALUES
    ('192.168.0.1', 5),
    ('192.168.0.1', 9),
    ('192.168.0.2', 3),
    ('192.168.0.2', 3),
    ('192.168.0.3', 5),
    ('192.168.0.4', 9),
    ('192.168.0.4', 9);

-- Deprecated GROUP BY ALL syntax, do not use
SELECT
    e.ServerIP,
    COUNT_BIG(*)
FROM @Example AS e
WHERE
    e.VID >= 9
GROUP BY ALL
    e.ServerIP

-- Equivalent query plan
SELECT
    U.ServerIP,
    COUNT_BIG(U.Union1001)
FROM 
(
    SELECT
        e.ServerIP,
        NULL AS Union1001
    FROM @Example AS e
    UNION ALL
    SELECT
        e.ServerIP,
        0 AS Union1001
    FROM @Example AS e
    WHERE
        e.VID >= 9
) AS U
GROUP BY
    U.ServerIP

[SCAN COMPLETE]

SELECT    U.ServerIP, BASESCORE   COUNT_BIG(U.Union1001)
FROM 
(SELECT e.ServerIP, NULL AS Union1001
FROM @Example AS e
LEFT JOIN VULNS D ON d.vid = e.vid
WHERE        d.BASESCORE >= 9
AND EXPLOITABILITY IN('H','F','POC')

    UNION ALL    

SELECT e.ServerIP, 0 AS Union1001, BASESCOREFROM @Example AS e
LEFT JOIN VULNS D ON d.vid = e.vid
WHERE        d.BASESCORE >= 9
AND EXPLOITABILITY IN('H','F','POC')
) AS UGROUP BY U.ServerIP, BASESCORE

SELECT SERVERIP, 9 AS BASESCOREVALUE, SUM(CASE WHEN (b.BASESCOREVALUE >= 9 and exploitabiltiy IN('H','F','POC') )
THEN 1 ELSE 0 END) [COUNT]
FROM EXAMPLETABLE A
LEFT JOIN VULNS B ON b.vid = a.vid)
GROUP BY SERVERIP
ORDER BY [COUNT] DESC

SELECT 
    S.SERVERIP, 
    ISNULL(C.VIDCOUNT, 0) AS VIDCOUNT
FROM 
(
    -- Distinct server list
    SELECT DISTINCT 
        vs.SERVERIP 
    FROM dbo.VID_SERVER AS vs
) AS S
CROSS APPLY
(
    -- Most recent scan for each server
    SELECT TOP (1) 
        vs2.SCANCOMPLETE
    FROM dbo.VID_SERVER AS vs2
    WHERE
        -- Correlate to distinct server list
        vs2.SERVERIP = S.SERVERIP
    ORDER BY 
        vs2.SCANCOMPLETE DESC
) AS SC
OUTER APPLY
(
    -- Count qualifying VIDs
    SELECT 
        COUNT_BIG(DISTINCT vs3.VID) AS VIDCOUNT
    FROM dbo.VID_SERVER AS vs3
    JOIN dbo.VULNS AS v ON
        v.VID = vs3.VID
    WHERE 
        -- Correlate to distinct server list
        vs3.SERVERIP = S.SERVERIP
        -- Correlate to latest scan date
        AND vs3.SCANCOMPLETE = SC.SCANCOMPLETE
        -- Qualifying exploitabilities
        AND vs3.EXPLOITABILITY IN ('H', 'F', 'POC')
        -- Minimum base score value from VULNS
        AND v.BASESCOREVALUE >= 9
) AS C;

SERVERIP 	[BASESCORE]	[COUNT OF CRIT VIDs]
192.168.0.1	9 	 	5
192.168.0.2	8 	 	4
192.168.0.3	7 	 	6
192.168.0.4	8 	 	3
192.168.0.5	1 	 	3

SELECT     S.SERVERIP, c.BASESCORE, ISNULL(C.VIDCOUNT, 0) AS VIDCOUNTFROM 
(   
 -- Distinct server list    
SELECT DISTINCT         vs.SERVERIP
FROM dbo.VID_SERVER AS vs
) AS S
CROSS APPLY
(
-- Most recent scan for each server    
SELECT TOP (1)         vs2.SCANCOMPLETE    
FROM dbo.VID_SERVER AS vs2    
WHERE        
-- Correlate to distinct server list       
 vs2.SERVERIP = S.SERVERIP    
ORDER BY         vs2.SCANCOMPLETE DESC
) AS S
COUTER APPLY
(   
 -- Count qualifying VIDs    
SELECT         LEFT(v.basescore,1) AS BASESCORE, COUNT_BIG(DISTINCT vs3.VID) AS VIDCOUNT    
FROM dbo.VID_SERVER AS vs3    
JOIN dbo.VULNS AS v ON        v.VID = vs3.VID    
WHERE         
-- Correlate to distinct server list        
vs3.SERVERIP = S.SERVERIP        
-- Correlate to latest scan date        
AND vs3.SCANCOMPLETE = SC.SCANCOMPLETE        
-- Qualifying exploitabilities        
AND vs3.EXPLOITABILITY IN ('H', 'F', 'POC')        
-- Minimum base score value from VULNS       
 AND v.BASESCOREVALUE >= 9) AS C;