Need COUNT() to return "0" 1

[KevinFSI]

I need to query the number of OPEN issues for a given employee. I want to return their last name and the number of issues WHERE issue_status is not "6" (6 is a closed issue)

The issues and the employees are in separate db's. They are linked by employee number. In the employee table the field is employee_id. In the issue table the field is issue_assigned_to.

Are ya wit me so far?

I want this thing to return a "0" if there are no issues that meet the criteria. Instead, it's just not returning a record. Here's the query:

SELECT e.last_name, COUNT(i.issue_id) AS issues
FROM .issues_current.dbo.issue i
LEFT OUTER JOIN employee e
ON i.issue_assigned_to = e.employee_id
WHERE issue_assigned_to = '7682'
AND i.issue_status <> 6
GROUP BY e.last_name

Can anyone help me out on this? Kevin
slanek@ssd.fsi.com

 

[jhall156]

SELECT e.last_name, COUNT(i.issue_id) AS issues
FROM employee e
LEFT OUTER JOIN issues_current.dbo.issue i ON i.issue_assigned_to = e.employee_id
WHERE i.issue_assigned_to = '7682'
AND i.issue_status <> 6
GROUP BY e.last_name

I think this is what you want if you want each employee and the count of open issues. LEFT OUTER JOIN means give me all where filtered records from the left table and only those records on the right that meet the join criteria.

 

[KevinFSI]

Yeah, I tried that too. Still returns no records. I'm using myself as a test employee (my ID is 7682) so I know I'm in there AND if I take out the <>6 part, I get a result becaues I have a few issues that are assigned to me, but are now closed. The problem is when I add the <>6 part in there, the query returns nothing instead of returning my name with a count of &quot;0&quot;

Thanks for the suggestion though. Anything else would be greatly appreciated. Kevin
slanek@ssd.fsi.com

 

[jhall156]

Try this
SELECT e.last_name, SUM(CASE i.issue_id WHEN 6 THEN 0 ELSE 1 END) AS issues
FROM employee e
LEFT OUTER JOIN issues_current.dbo.issue i ON i.issue_assigned_to = e.employee_id
WHERE e.employee_id = '7682'
GROUP BY e.last_name

 

[pyttviper]

use group by all to return all record results it will return 0 and others

select info
from db
where <>6
group by all emps

 

[KevinFSI]

I am using group by. See above query...last line. Kevin
slanek@ssd.fsi.com

 

[jhall156]

Did you try the query with sum() I suggested?

 

[KevinFSI]

J, that did it. Thanks a million! Kevin
slanek@ssd.fsi.com

 

[j9]

OR, something similar:

SELECT e.last_name,
CASE WHEN COUNT(i.issue_id) is null then 0
ELSE COUNT(i.issue_id)
End AS issues
FROM .issues_current.dbo.issue i
LEFT OUTER JOIN employee e
ON i.issue_assigned_to = e.employee_id
WHERE issue_assigned_to = '7682'
AND i.issue_status <> 6
GROUP BY e.last_name

 

[KevinFSI]

That one didn't return anything. Kevin
slanek@ssd.fsi.com

 

[j9]

OOPS. You're right-I think I should've changed your code to 'right outer join' instead of left.

 

[pyttviper]

(GROUP BY ALL) will return &quot;0&quot; count along with all other record counts if tou are using T-sql

your above queries do not use this

try this one but with an inner join

SELECT e.last_name, COUNT(i.issue_id) AS issues
FROM .issues_current.dbo.issue i
LEFT OUTER JOIN employee e
ON i.issue_assigned_to = e.employee_id
WHERE issue_assigned_to = '7682'
AND i.issue_status <> 6
GROUP BY ALL e.last_name

 

[KevinFSI]

j9, still a no-go with the RIGHT OUTER.

pytt, this time the query returned over 600 records with, all with zeros. Kevin
slanek@ssd.fsi.com

 

[pyttviper]

was that with an inner or left or right join?

 

[KevinFSI]

Tried all.

left inner
left outer
right inner
right outer
etc... Kevin
slanek@ssd.fsi.com

 

[pyttviper]

just an inner join?

 

[jhall156]

inner is the same whether right or left. The problem is in your where clause your limiting both sides because you're using the right side instead of the left to reduce the result set to one employee_id (yours) just change it to e.employee_id = <yourid> and you should be kosher with the left join.

 

[plantj]

Point 1: You need employee always, regardless of there being an open issue. Therefore you need

from
employee
left join issue
on....

Point 2: You cannot apply conditions to issue in the WHERE clause otherwise the LEFT join effectively becomes an (INNER) JOIN.

from
employee e
left join issue i
on i.issue_assigned_to = e.employee_id
and i.issue_status <> 6

...NOT...

from
employee e
left join issue i
on i.issue_assigned_to = e.employee_id
where
i.issue_status <> 6

 

[tlbroadbent]

The problem is the where criteria. When you test for issue_assigned_to = '7682' or issue_status <> 6, the query cannot find zero records where these conditions are true. You need to test for a NULL condition in addition to the other criteria. If you want to list all employees then omit the test for issue_assigned_to = '7682' as this limits the result to one employee_id at most.

Try this query.

SELECT e.last_name, COUNT(i.issue_id) AS issues
FROM employee e
LEFT OUTER JOIN issue i
ON i.issue_assigned_to = e.employee_id
WHERE (i.issue_status <> 6 Or i.issue_status Is Null)
GROUP BY e.last_name Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.