Join and conditional count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Join and conditional count

Author

Topic

ryanoc333
Starting Member

4 Posts

Posted - 2006-12-21 : 10:14:29

I have 2 table that I want to join and output a row on a condition that one of the records have a null in the field. Heres what I have.

employee table (empid, name)
tasks table (taskid, empid, taskname, resolution)

If the resolution is null than I want it to be accounted for in each employee record. Heres my query so far that joins the 2 tables and accounts for each employee and counts each task they have. I need another column that counts the tasks.resolution's null values for each employee but cant figure it out. Thanks for any help!

SELECT e.empid,
e.name,
COUNT(t.ID) as 'tcount'
FROM tasks t
RIGHT JOIN employee e ON c.empid = t.empid
GROUP BY e.empid, e.name
order by 'tcount' desc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 10:19:41

select e.empid, e.ename, count(*) as 'tcount', sum(case when t.resolution is null then 1 else 0 end) as 'NULL resolution'
from employee as e
left join tasks as t on t.empid = e.empid
group by e.empid, e.ename
order by count(*) desc

select e.empid, e.ename, count(*) as 'tcount', sum(case when t.resolution is null then 1 else 0 end) as 'NULL resolution'
from employee as e
left join tasks as t on t.empid = e.empid
group by e.empid, e.ename
order by 3 desc

Peter Larsson
Helsingborg, Sweden

ryanoc
Starting Member

25 Posts

Posted - 2006-12-21 : 10:36:16

Great, thanks! only one more thing, in the results there are empids that dont have records in the tasks table but they show a '1' in the null resolution column. The empid that are in the task table are showing corectly though.

Ryan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 10:38:54

select e.empid, e.ename, count(*) as 'tcount', sum(case when t.resolution is null and t.empid is not null then 1 else 0 end) as 'NULL resolution'
from employee as e
left join tasks as t on t.empid = e.empid
group by e.empid, e.ename
order by 3 desc

Peter Larsson
Helsingborg, Sweden

ryanoc
Starting Member

25 Posts

Posted - 2006-12-21 : 10:41:12

Nice! you are the man!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources