CODEWITHSUNDEEP
sql
maltad
maltad

Reputation: 7

Problem with count in SQL query

I want to create a query that show the name of the employee and the sum of all service that aren't not closed. The result of the query is not giving me the correct value.

Here is my query:

SELECT DISTINCT 
       a.AssignedEmp, 
       COUNT( a.TipoStatus ) AS 'Service Request Count'
   FROM Service a, 
        employee b  
  WHERE b.Classification_ClassificationID = 2 
    AND a.TipoStatus = 'Open'
     OR a.TipoStatus = 'Pending'
     OR a.TipoStatus = 'Hold'
     OR a.TipoStatus = 'Warranty'
    AND a.AssignedEmp = b.UserName
 GROUP BY a.AssignedEmp
    LIMIT 0, 30

There result that this query gives me is: 

dpadil    16
epadil     8

The correct values are supposed to be 2 and 1.

Upvotes: 0

Views: 187

Answers (3)

Jonathan Leffler
Jonathan Leffler

Reputation: 753870

You need brackets around your OR'd conditions (and should use JOIN notation):

SELECT DISTINCT 
       a.AssignedEmp, 
       COUNT( a.TipoStatus ) AS 'Service Request Count'
  FROM Service AS a
  JOIN employee AS b ON a.AssignedEmp = b.UserName
 WHERE b.Classification_ClassificationID = 2 
   AND (a.TipoStatus = 'Open'
    OR  a.TipoStatus = 'Pending'
    OR  a.TipoStatus = 'Hold'
    OR  a.TipoStatus = 'Warranty')
 GROUP BY a.AssignedEmp
 LIMIT 0, 30

Or you can write the condition directly in terms of 'not closed':

SELECT DISTINCT 
       a.AssignedEmp, 
       COUNT( a.TipoStatus ) AS 'Service Request Count'
  FROM Service AS a
  JOIN employee AS b ON a.AssignedEmp = b.UserName
 WHERE b.Classification_ClassificationID = 2 
   AND a.TipoStatus != 'Closed'
 GROUP BY a.AssignedEmp
 LIMIT 0, 30

As originally written, the WHERE clause was equivalent to:

WHERE (b.Classification_ClassificationID = 2 AND a.TipoStatus = 'Open')
   OR a.TipoStatus = 'Pending'
   OR a.TipoStatus = 'Hold'
   OR (a.TipoStatus = 'Warranty' AND a.AssignedEmp = b.UserName)

This counts all the Open service items that have a classification ID of 2 for the user; it also counts all the Pending service items regardless of classification ID, and all the Hold service items regardless of classification ID, and all the Warranty service items assigned to the user, regardless of classification ID. The GROUP BY filters things so that only the items with the right assigned employee are counted, but there is a partial cross-product for some of the terms, leading to the inflated counts.

Upvotes: 2

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28718

You probably want to structure your condition a bit more explicitly.

You should also specify the JOIN condition using a JOIN statement, rather than an additional condition.

SELECT DISTINCT         
a.AssignedEmp,         
COUNT( a.TipoStatus ) AS 'Service Request Count'    
FROM Service a
       INNER JOIN employee b
       ON a.AssignedEmp = b.UserName  
WHERE b.Classification_ClassificationID = 2      
    AND (
        a.TipoStatus = 'Open'      
        OR a.TipoStatus = 'Pending'      
        OR a.TipoStatus = 'Hold'      
        OR a.TipoStatus = 'Warranty'
        )
GROUP BY a.AssignedEmp     
    LIMIT 0, 30

Upvotes: 0

paxdiablo
paxdiablo

Reputation: 881503

The first thing you should do is get rid of the count and the group by.

This will give you the actual rows that are being used and will probably show you the problem straight up.

The fact that the ratios are correct (16:8 = 2:1) usually indicates that AND a.AssignedEmp = b.UserName is not as limiting as you think.

In other words, the cross-table joins are more prolific. Getting the rows themselves out should help you understand why.

Upvotes: 3

Related Questions