join and conditional
-
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
-
Ryan
Assuming the taskid column doesn't allow nulls, something like this:
SELECT e.empid,
e.name,
COUNT(t.taskID) as tcount,
COUNT(t.taskID) - COUNT(t.resolution) as NullRes
FROM tasks t
RIGHT JOIN employee e ON e.empid = t.empid
GROUP BY e.empid, e.name
order by tcount desc
John
-
Thanks. That was a simpler answer that another I got at another message board. Works great!
Ryan
-
What was the other answer?
-
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
-
Not that much more complicated... but it may very well seem so if it's the first time you meet this code
. -
not too much. I may use the case way since I can add other conditionals to it like empty string checks.
-
I don't know if this would work, but it's a thought:
SELECT e.empid,
e.name,
COUNT(t.ID) as 'tcount'
FROM tasks t
RIGHT JOIN employee e ON c.empid = t.empid Or t.resolution Is Null
GROUP BY e.empid, e.name
order by 'tcount' desc
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply