Hi Everyone,

I've got a question and I think the answer can be a single SQL statement.
In fact, I think I might've done this before, but I can't find the SQL.

I've got a table with names and different bugs and status ids. I'd like to
get a query that totals the various statuses. For instance, the table is

Name bugid status
Jeff 12 open
Monica 13 new
Jeff 10 closed
George 27 closed
....

and I'd like the output of the query to contain the count of the various
statuses in a single row

Name new open closed
Jeff 10 2 12
Monica 1 3 17
George 12 1 14
.....

I've can't seem to figure out how to aggregate all this properly. Does
anyone have an answer or some pointers?

Thanks!

Hi

Try something like

SELECT Name,
SUM(CASE WHEN STATUS <> 'open' AND STATUS <> 'closed' THEN 1 ELSE 0 END) as
New,
SUM(CASE WHEN STATUS = 'open' THEN 1 ELSE 0 END) as Open,
SUM(CASE WHEN STATUS = 'closed' THEN 1 ELSE 0 END) as Closed,
FROM MyTable
GROUP BY Name

John
[quoted text, click to view]

Assuming that you can only have one row per user per bug (posting DDL helps
clarify this), then you can do something like this:

create table #t (
UserName sysname,
BugId int,
Status char(6),
constraint PK_t primary key (UserName, BugId)
)

insert into #t
(UserName, BugId, Status)
select 'Jeff', 12, 'Open'
insert into #t
(UserName, BugId, Status)
select 'Monica', 13, 'New'
insert into #t
(UserName, BugId, Status)
select 'Monica', 26, 'New'
insert into #t
(UserName, BugId, Status)
select 'Jeff', 10, 'Closed'
insert into #t
(UserName, BugId, Status)
select 'George', 27, 'Closed'

select
UserName,
sum(case when Status = 'New' then 1 else 0 end) as 'New',
sum(case when Status = 'Open' then 1 else 0 end) as 'Open',
sum(case when Status = 'Closed' then 1 else 0 end) as 'Closed'
from
#t
group by
UserName

drop table #t

Simon

[quoted text, click to view]