Problem with count in SQL query

Question

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.

@OMG, you are aware that there are other DBMS', surely? :-) — paxdiablo
– paxdiablo, Commented Feb 4, 2011 at 2:23
@paxdiablo: Only three that I know support LIMIT. And don't call me "Shirley" :p — OMG Ponies
– OMG Ponies, Commented Feb 4, 2011 at 2:26

paxdiablo · Accepted Answer · 2011-02-04 02:20:54Z

3

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.

answered Feb 4, 2011 at 2:20

paxdiablo

889k243 gold badges1.6k silver badges2k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jonathan Leffler · Accepted Answer · 2011-02-04 07:32:47Z

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.

This works perfect!! Thank you Jonathan and everybody that helps!!

Kirk Broadhurst · Accepted Answer · 2011-02-04 02:23:20Z

0

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

answered Feb 4, 2011 at 2:23

Kirk Broadhurst

28.9k20 gold badges111 silver badges179 bronze badges

Collectives™ on Stack Overflow

Problem with count in SQL query

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related