Clever Geek Handbook

SQL: how to get COUNT when using EXCEPT

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 trying to get the account of companies from this list back. If I remove the counter and just select "S.Company" I get 2 values ​​(I would like "COUNT = 2"), but with the count it appears as 34 records (COUNT = 34).

Any help would be appreciated. Thank!

+3


source to share


1 answer


wrap it all up in "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
+7


source






More articles:


All Articles