Posted by David Portas on 01/13/06 23:42

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
--

[Back to original message]