Another grouping question

[Craig Jennings]

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!

[John Bell]

Re: Another grouping question

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@nosp am.yahoo.com> wrote in message
news:9e460b3016 2bef361d831e6b9 117081e@free.te ranews.com...[color=blue]
> 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!
>
>[/color]

[Simon Hayes]

Re: Another grouping question

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@nosp am.yahoo.com> wrote in message
news:9e460b3016 2bef361d831e6b9 117081e@free.te ranews.com...[color=blue]
> 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!
>
>[/color]

[John Bell]

Re: Another grouping question

And that's a pint for Simon as well :)

John

"Craig Jennings" <cjennings@nosp am.yahoo.com> wrote in message
news:99d61b89d1 3e536709cd0b671 bf06b88@free.te ranews.com...[color=blue]
>
> Yes! Yes! That's it! Thanks! I feel like I owe you a beer!
>
> -- Craig
>
> John Bell wrote:[color=green]
> > Hi
> >
> > Try something like
> >
> > SELECT Name,
> > SUM(CASE WHEN STATUS <> 'open' AND STATUS <> 'closed' THEN 1 ELSE 0[/color][/color]
END) as[color=blue][color=green]
> > 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@nosp am.yahoo.com> wrote in message
> > news:9e460b3016 2bef361d831e6b9 117081e@free.te ranews.com...
> >[color=darkred]
> >>Hi Everyone,
> >>
> >>I've got a question and I think the answer can be a single SQL[/color][/color][/color]
statement.[color=blue][color=green][color=darkred]
> >>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[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> >>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!
> >>
> >>[/color]
> >
> >
> >[/color]
>[/color]