CODEWITHSUNDEEP
sqlsql-server
Sung
Sung

Reputation: 209

How can I exclude COUNT results which are 0?

I have a query that gets a count of all the tickets assigned to a team:

SELECT 'Application Developers' AS team, COUNT(Assignee) AS tickets
from mytable WHERE status = 'Open' AND Assignee like '%Application__bDevelopers%'

UNION ALL

SELECT 'Desktop Support' AS team, COUNT(Assignee) AS tickets
from mytable WHERE status = 'Open' AND Assignee like '%Desktop__bSupport%'

UNION ALL

SELECT 'Network Management' AS team, COUNT(Assignee) AS tickets
FROM mytable WHERE status = 'Open' AND Assignee LIKE '%Network__bManagement%'

UNION ALL

SELECT 'Security' AS team, COUNT(Assignee) AS tickets
from mytable WHERE status = 'Open' AND Assignee = '%Security%'

UNION ALL

SELECT 'Telecom' AS team, COUNT(Assignee) AS tickets
from mytable WHERE status = 'Open' AND Assignee = '%Telecom%'

The result is:

        team              tickets
Application Developers       6
Desktop Support             374
Network Management           0
Security                     7
Telecom                      0

How can I exclude the results that come back with "0" tickets?

Upvotes: 1

Views: 3701

Answers (4)

mindbdev
mindbdev

Reputation: 444

You can use Common table expressions instead of using Having

;WITH CTE AS
(
SELECT 'Application Developers' AS team, COUNT(Assignee) AS tickets
from mytable WHERE status = 'Open' AND Assignee like '%Application__bDevelopers%'

UNION ALL

SELECT 'Desktop Support' AS team, COUNT(Assignee) AS tickets
from mytable WHERE status = 'Open' AND Assignee like '%Desktop__bSupport%'

UNION ALL

SELECT 'Network Management' AS team, COUNT(Assignee) AS tickets
FROM mytable WHERE status = 'Open' AND Assignee LIKE '%Network__bManagement%'

UNION ALL

SELECT 'Security' AS team, COUNT(Assignee) AS tickets
from mytable WHERE status = 'Open' AND Assignee = '%Security%'

UNION ALL

SELECT 'Telecom' AS team, COUNT(Assignee) AS tickets
from mytable WHERE status = 'Open' AND Assignee = '%Telecom%'

)
SELECT 
*
FROM CTE
WHERE tickets>0

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93754

Instead of using Union all between bunch of queries Change your query like this.

Use Having clause to filter the group's where count <> 0

SELECT CASE
         WHEN Assignee LIKE '%Application__bDevelopers%' THEN 'Application Developers'
         WHEN Assignee LIKE '%Desktop__bSupport%' THEN 'Desktop Support'
         WHEN Assignee LIKE '%Network__bManagement%' THEN 'Network Management'
         WHEN Assignee LIKE '%Security%' THEN 'Security'
         WHEN Assignee LIKE '%Telecom%' THEN 'Telecom'
       END             AS team,
       Count(Assignee) AS tickets
FROM   mytable
WHERE  status = 'Open'
       AND ( Assignee LIKE '%Application__bDevelopers%'
              OR Assignee LIKE '%Desktop__bSupport%'
              OR Assignee LIKE '%Network__bManagement%'
              OR Assignee = '%Security%'
              OR Assignee = '%Telecom%' )
GROUP  BY CASE
            WHEN Assignee LIKE '%Application__bDevelopers%' THEN 'Application Developers'
            WHEN Assignee LIKE '%Desktop__bSupport%' THEN 'Desktop Support'
            WHEN Assignee LIKE '%Network__bManagement%' THEN 'Network Management'
            WHEN Assignee LIKE '%Security%' THEN 'Security'
            WHEN Assignee LIKE '%Telecom%' THEN 'Telecom'
          END
HAVING Count(Assignee) <> 0

Upvotes: 1

sjagr
sjagr

Reputation: 16512

You can use HAVING for each query:

SELECT 'Application Developers' AS team, COUNT(Assignee) AS tickets
from mytable WHERE
    status = 'Open'
    AND Assignee like '%Application__bDevelopers%'
HAVING COUNT(Assignee) <> 0

I'd suggest @NoDisplayName's answer because your query as it stands is quite inefficient.

Upvotes: 4

M.Ali
M.Ali

Reputation: 69594

Another way of writing this query would be to avoid all of these UNION ALL queries and do something like 

SELECT Teams 
      ,Tickets 
FROM 
(
SELECT NULLIF(COUNT(CASE WHEN Assignee LIKE '%Application__bDevelopers%' 
                     THEN 1 ELSE NULL END),0) AS [Application__bDevelopers]
      ,NULLIF(COUNT(CASE WHEN Assignee LIKE '%Desktop__bSupport%' 
                     THEN 1 ELSE NULL END),0) AS [Desktop__bSupport]
      ,NULLIF(COUNT(CASE WHEN Assignee LIKE '%Network__bManagement%' 
                     THEN 1 ELSE NULL END),0) AS [Network__bManagement]
      ,NULLIF(COUNT(CASE WHEN Assignee LIKE '%Security%' 
                     THEN 1 ELSE NULL END),0) AS [Security]
      ,NULLIF(COUNT(CASE WHEN Assignee LIKE '%Telecom%' 
                     THEN 1 ELSE NULL END),0) AS [Telecom]
FROM mytable
WHERE [status] = 'Open' ) Q
 UNPIVOT (Tickets FOR Teams IN ([Application__bDevelopers], [Desktop__bSupport]
                               ,[Network__bManagement],[Security],[Telecom]))up

Upvotes: 1

Related Questions