Posted by teresa.masino@peninsula.org on 01/13/06 21:43

You need to do a self join on the table. Without your table
definition, it would be something like this

SELECT TI1.Tech,
Num_Assigned = COUNT(TI1.TechID),
Num_On_Hold = COUNT(TI2.TechID)
FROM TechIssues TI1, TechIssues TI2
WHERE TI1.TechID = TI2.TechID
GROUP BY TI1.Tech

Now, the above assumes that all techs have issues assigned AND issues
on HOLD. You'd need to UNION a couple more of these to handle where
Techs have records assigned but not on hold and vice versa. But this
should get you started.

Hope it helps
Teresa Masino

• Next in forum: Seeking correct syntax to pass a NULL value for a datetime field from an asp page to SQL
• Prev in forum: Deadlocks, why?
• Thread view: Re: Baffled! Can't figure out how to do this query. Is it even possible?

[Reply to this message]