Baffled! Cant figure out how to do this query. Is it even possible?

I have an "Issues" table for my technicians. An issue can be on "hold"
or "assigned".
I want to get a count for each tech with a column showing number of
issues on hold and a column for number of issues assigned. It would
look like this --

Tech Num_Assigned Num_On_Hold
Fred 3 10
Carol 6 7

I can get each column separately, but I want both in the same answer
table!
Is that too much to ask? :)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|||jonescv@.gw.ccsd.net wrote:
> I have an "Issues" table for my technicians. An issue can be on "hold"
> or "assigned".
> I want to get a count for each tech with a column showing number of
> issues on hold and a column for number of issues assigned. It would
> look like this --
> Tech Num_Assigned Num_On_Hold
> Fred 3 10
> Carol 6 7
>
> I can get each column separately, but I want both in the same answer
> table!
> Is that too much to ask? :)

Here's a guess:

SELECT tech,
COUNT(CASE WHEN status = 'assigned' THEN 1 END),
COUNT(CASE WHEN status = 'hold' THEN 1 END)
FROM your_table
GROUP BY tech ;

--
David Portas
SQL Server MVP
--