Someone

SELECT 
scan.ip,
scan.hostname
(SELECT 
count( p.severity ) 
FROM 
patches AS p, 
scan AS s
WHERE 
p.scanid = scan.scanid 
ANDp.severity = 'warnings'
ands.ip = scan.ip) as 'warning'
(SELECT 
count( p.severity ) 
FROM 
patches AS p, 
scan AS s
WHERE 
p.scanid = scan.scanid 
ANDp.severity = 'critical'
ands.ip = scan.ip) as 'critical'

FROM
scan
ORDER BY 
scan.ip