CODEWITHSUNDEEP
sqlsql-serverdatabase
Katy Strahan
Katy Strahan

Reputation: 51

Selecting count by row combinations

I'm strugling with what on the first sight appeared to be simple SQL query :)

So I have following table which has three columns: PlayerId, Gender, Result (all of type integer).

What I'm trying to do, is to select distinct players of gender 2 (male) with number of each results. There are about 50 possible results, so new table should have 51 columns:

|PlayerId | 1 | 2 | 3 | ... | 50 |

So I would like to see how many times each individual male (gender 2) player got specific result.

*** In case question is still not entirely clear to you: After each game I insert a row with a player ID, gender and result (from 1 - 50) player achieved in that game. Now I'd like to see how many times each player achieved specfic results.

Upvotes: 0

Views: 96

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

If there are 50 results and you want them in columns, then you are talking about a pivot. I tend to do these with conditional aggregation:

select player,
       sum(case when result = 0 then 1 else 0 end) as result_00,
       sum(case when result = 1 then 1 else 0 end) as result_01,
       . . .
       sum(case when result = 50 then 1 else 0 end) as result_50
from t
group by player;

You can choose a particular gender if you like, with where gender = 2. But why not calculate all at the same time?

Upvotes: 2

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

The easiest way is to use pivoting:

;with cte as(Select * from t
             Where gender = 2)
Select * from cte
Pivot(count(gender) for result in([1],[2],[3],....,[50]))p

Fiddle http://sqlfiddle.com/#!3/8dad5/3

One note: keeping gender in scores table is a bad idea. Better make a separate table for players and keep gender there.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

try 

select  player, result, count(*)  
from your_table 
where Gender = 2
group by player, result;

Upvotes: 1

Shree29
Shree29

Reputation: 644

select PleyerId from tablename where result = 'specific result you want' and gender = 2 group by PleyerId

Upvotes: 0

Related Questions