CODEWITHSUNDEEP
sqlsql-server
Aaron
Aaron

Reputation: 1455

Count with exists in SQL

Why is this query not returning the count of the results? How do I get it to show the count

SELECT COUNT (*) AS MWith
FROM member   m
JOIN Channel  mc ON mc.MemberID = m.id
JOIN Client   c  ON c.id = m.clientid
JOIN packages p  ON p.id = m.packageid
WHERE Enroll > '2018'
AND EXISTS  (
            SELECT * FROM
            activity a 
            WHERE a.memberid = m.id
            AND a.code IN ('785', 'a599')
            ) 
GROUP BY m.id;

OUTPUT

MWith
1
1
1

Upvotes: 0

Views: 94

Answers (1)

Alexander Volok
Alexander Volok

Reputation: 5940

The empty set is because of the clause group by .

The workarounds are:

  1. Remove a GROUP BY, because m.id anyway is not part of the output
  2. Use GROUP BY ALL

An original example:

SELECT COUNT(*) AS MWith
FROM member m
    JOIN Channel mc
        ON mc.MemberID = m.id
    JOIN Client c
        ON c.id = m.clientid
    JOIN packages p
        ON p.id = m.packageid
WHERE Enroll > '2018'
      AND EXISTS
(
    SELECT *
    FROM activity a
    WHERE a.memberid = m.id
          AND a.code IN ( '785', 'a599' )
)
--   GROUP BY m.id;

Other, simpler examples to show a difference:

-- returns an empty resultset
SELECT COUNT(*) FROM sys.databases
WHERE 1=0
GROUP BY name

-- returns: a single row with 0
SELECT COUNT(*) FROM sys.databases
WHERE 1=0

-- Another example with GROUP BY ALL
-- it returns one row per grouped value, with expected count = 0
SELECT COUNT(*) FROM sys.databases
WHERE 1=0
GROUP BY ALL name

Upvotes: 1

Related Questions