I have a table in SQL Server that looks like this:

Name Status
---------------------------
Steve Complete
Steve In Queue
John Pending
John Complete
John Complete
Tim In Queue

I need to throw a query at this that will return the number of
"Complete"'s that each person has. So the result would look like:

Name Completed
----------------------------
Steve 1
John 2
Tim 0

Can anyone out with what a query statement would look like to get
the desired output?


*** Sent via Developersdex http://www.developersdex.com ***

I think this should work:


Select Name, Count (Status)
from <<tablename>>
where status ='complete'
group by Name
order by Name



"Terry Olsen" <tolsen64@hotmail.com> wrote in message
news:46f19892$0$508$815e3792@news.qwest.net...
>I have a table in SQL Server that looks like this:
>
> Name Status
> ---------------------------
> Steve Complete
> Steve In Queue
> John Pending
> John Complete
> John Complete
> Tim In Queue
>
> I need to throw a query at this that will return the number of
> "Complete"'s that each person has. So the result would look like:
>
> Name Completed
> ----------------------------
> Steve 1
> John 2
> Tim 0
>
> Can anyone out with what a query statement would look like to get
> the desired output?
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

Yes, that works. But it doesn't show the row with no completes. So
unless a person has at least one complete, they won't show.

This will work for me, but is there a way to get it to include the
people with no completes?

Thanks again!


*** Sent via Developersdex http://www.developersdex.com ***

Terry Olsen wrote:

> Yes, that works. But it doesn't show the row with no completes. So
> unless a person has at least one complete, they won't show.
>
> This will work for me, but is there a way to get it to include the
> people with no completes?

select Name, count(case Status when 'Complete' then 1 else 0 end)
from <<tablename>>
group by Name
order by Name

Ed Murphy wrote:
> Terry Olsen wrote:
>
>> Yes, that works. But it doesn't show the row with no completes. So
>> unless a person has at least one complete, they won't show.
>> This will work for me, but is there a way to get it to include the
>> people with no completes?
>
> select Name, count(case Status when 'Complete' then 1 else 0 end)
> from <<tablename>>
> group by Name
> order by Name

I guess you meant


select Name, sum(case Status when 'Complete' then 1 else 0 end)
from <<tablename>>
group by Name
order by Name

--
Arto Viitanen

Arto V Viitanen wrote:

> Ed Murphy wrote:
>> Terry Olsen wrote:
>>
>>> Yes, that works. But it doesn't show the row with no completes. So
>>> unless a person has at least one complete, they won't show.
>>> This will work for me, but is there a way to get it to include the
>>> people with no completes?
>> select Name, count(case Status when 'Complete' then 1 else 0 end)
>> from <<tablename>>
>> group by Name
>> order by Name
>
> I guess you meant
>
>
> select Name, sum(case Status when 'Complete' then 1 else 0 end)

Yes, sorry.