SQL: How to get COUNT when using EXCEPT

Question

SELECT 'COUNT=' + CONVERT(VARCHAR(64), COUNT(s.company)) AS sites 
FROM   site s 
WHERE  s.sitetype = 'om' 
       AND s.status = 1 
EXCEPT 
SELECT DISTINCT sg.company 
FROM   snmpmibdevice AS sdevice 
       JOIN site sg 
         ON sg.guid = sdevice.siteguid 
       JOIN snmpmibdata sdata 
         ON sdata.snmpmibdeviceguid = sdevice.snmpmibdeviceguid 
WHERE  sdata.sampletimestamp > Dateadd (mi, -15, Getutcdate()) 
       AND sg.sitetype = 'OM' 

Basically I am attempting to return a count of company names from this. If I remove the count and just select "S.Company" I will get 2 values (I would like "COUNT=2"), but with the count it comes up as 34 records (COUNT=34).

I would appreciate any help. Thanks!

Answer 1

wrap the whole thing in a "select count(*) from ()"

SELECT 'COUNT=' + CONVERT(VARCHAR(64), COUNT(company)) AS sites 
FROM (

    SELECT s.company
    FROM   site s 
    WHERE  s.sitetype = 'om' 
           AND s.status = 1 
    EXCEPT 
    SELECT DISTINCT sg.company 
    FROM   snmpmibdevice AS sdevice 
           JOIN site sg 
             ON sg.guid = sdevice.siteguid 
           JOIN snmpmibdata sdata 
             ON sdata.snmpmibdeviceguid = sdevice.snmpmibdeviceguid 
    WHERE  sdata.sampletimestamp > Dateadd (mi, -15, Getutcdate()) 
           AND sg.sitetype = 'OM' 
) a