Select count where question

[emblewembl]

I've got an sql database table which contains 2 columns, 'name' and 'position'.

The names aren't unique, i.e. there might be more than one row for mr smith, one with position = 2, one with position = 3.

I need to build a stored procedure that selects each distinct name, and a count of position 1's, 2's and 3's for each person. Can anyone help, i'm stuck!!!

Thanks.

i love chocolate

 

[Jamfool]

Create proc procname
as

select name,count(*)
from tbl
group by name

 

[SQLDenis]

create table bla (name varchar(50), position int)
insert into bla
select 'smith',1 union all
select 'smith',1 union all
select 'smith',2 union all
select 'smith',3 union all
select 'smith',1 union all
select 'joe',1 union all
select 'joe',2 union all
select 'joe',2 union all
select 'joe',2 union all
select 'joe',3 

-------------------------------------------
select name, position,count(*) as CountOf
from bla
group by [name],position
order by 1,2

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[emblewembl]

Hi sqldenis,

Thanks for the response - i've tried:

select name, position,count(*) as CountOf
from bla
group by [name],position
order by 1,2

and it does pretty much do what i want. I get a table back with columns name, position, count and it all contains the right data.

However - is there any way to get a table returned with columns:

name, pos1count, pos2count, pos3count

????



i love chocolate

 

[gmmastros]

try this...

Select Name,
       Sum(Case When Position = 1 Then 1 Else 0 End) As Pos1,
       Sum(Case When Position = 2 Then 1 Else 0 End) As Pos2,
       Sum(Case When Position = 3 Then 1 Else 0 End) As Pos3
From bla
Group By Name

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

here we go

select name, position,
case when position =1 then count(*) else 0 end as pos1,
case when position =2 then count(*) else 0 end as pos2,
case when position =3 then count(*) else 0 end as pos3
from bla
group by [name],position
order by 1,2

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[emblewembl]

SQLDenis - thanks that worked perfectly!

i love chocolate