sp help 1

[vlitim]

I have a table which looks like

contributor medal
1 1
2 1
3 3
4 0
1 2
1 1

what I need to do is have a query return the number of 1's,2's,3's each contributor has got?

 

[1209]

what exactly do u want if its number of contibuters who got 1's,2's,3's or is it the list of all those contributers ?
If it also includes the multiples of 2's and 3's.

 

[vlitim]

what I want is so that I can show this:

contributor 1 2 3
1 2 1 0

 

[rajeevnandanmishra]

Hi vlitim,
Try this
-----------------------------
select contributor,
sum(case medal when 1 then 1 else 0 end)as medal1,
sum(case medal when 2 then 1 else 0 end) as medal2,
sum(case medal when 3 then 1 else 0 end) as medal3
from myTable
group by contributor
-----------------------------


Hope it works!

 

[vlitim]

right finally got round to trying this. It is very close to what I want the problem is that it doesn't sum the medals properley. eg

if there are 3 gold medals then instead of showing 3 it will display 1.

so basically if the person has a medal then = 1 else 0

cheers
Tim

 

[Terryute]

Did you try

Select
Contributor,
Medal,
Count(*)
From Table
Group by Contributor,Medal
Order by Contributor

 

[vlitim]

the problem with that is that you get the same contributor shown multiple times if they have different medals!

 

[linda105]

what about
select t1.c, t1.num, t2.num, t3.num
from
(select contributor as c, count(*) as num
from table
where medal = 1
group by contributor
) as t1,

(select contributor as c, count(*) as num
from table
where medal = 2
group by contributor
) as t2,

(select contributor as c, count(*) as num
from table
where medal = 3
group by contributor
) as t3
where t1.c = t2.c and t2.c = t3.c

 

[vlitim]

that doesn't return any results at all!

 

[rajeevnandanmishra]

Hi vlitim,
I had tried the SQL statement and it is giving the same result as you expected i.e. for
contributor medal
1 1
2 1
3 3
4 0
1 2
1 1

SHOW :
contributor 1 2 3
1 2 1 0
---------------------------

Now please let me know if this is not what you require?
Please give a sample data you have and result you want.