Another grouping question

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
"Craig Jennings" <cjennings@.nospam.yahoo.com> wrote in message
news:9e460b30162bef361d831e6b9117081e@.free.teranew s.com...
> 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!|||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

"Craig Jennings" <cjennings@.nospam.yahoo.com> wrote in message
news:9e460b30162bef361d831e6b9117081e@.free.teranew s.com...
> 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!|||And that's a pint for Simon as well :)

John

"Craig Jennings" <cjennings@.nospam.yahoo.com> wrote in message
news:99d61b89d13e536709cd0b671bf06b88@.free.teranew s.com...
> Yes! Yes! That's it! Thanks! I feel like I owe you a beer!
> -- Craig
> John Bell wrote:
> > 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
> > "Craig Jennings" <cjennings@.nospam.yahoo.com> wrote in message
> > news:9e460b30162bef361d831e6b9117081e@.free.teranew s.com...
> >>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!
> >>
> >