Problem counting item frequency on T-SQL

Posted by Raúl Roa on Stack Overflow See other posts from Stack Overflow or by Raúl Roa
Published on 2010-04-14T21:53:17Z Indexed on 2010/04/14 22:13 UTC
Read the original article Hit count: 456

Filed under:
|
|

I'm trying to count the frequency of numbers from 1 to 100 on different fields of a table.

Let's say I have the table "Results" with the following data:

LottoId   Winner    Second    Third                                                                                                                                                                                                                                                            
--------- --------- --------- ---------
1         1         2         3                                                                                                                                                                                                                                                                
2         1         2         3 

I'd like to be able to get the frequency per numbers. For that I'm using the following code:

--Creating numbers temp table
CREATE TABLE #Numbers(
Number int)

--Inserting the numbers into the temp table 
declare @counter int
set @counter = 0
while @counter < 100
begin
  set @counter = @counter + 1
  INSERT INTO #Numbers(Number) VALUES(@counter)   
end

--
SELECT #Numbers.Number, Count(Results.Winner) as Winner,Count(Results.Second) as Second, Count(Results.Third) as Third FROM #Numbers
LEFT JOIN Results ON
#Numbers.Number = Results.Winner OR #Numbers.Number = Results.Second OR #Numbers.Number = Results.Third 
GROUP BY #Numbers.Number

The problem is that the counts are repeating the same values for each number. In this particular case I'm getting the following result:

Number    Winner    Second    Third                                                                                                                                                                                                                                                            
--------- --------- --------- ---------
1         2         2         2                                                                                                                                                                                                                                                                
2         2         2         2 
3         2         2         2
...

When I should get this:

Number    Winner    Second    Third                                                                                                                                                                                                                                                            
--------- --------- --------- ---------
1         2         0         0                                                                                                                                                                                                                                                                
2         0         2         0 
3         0         0         2
...

What am I missing?

© Stack Overflow or respective owner

Related posts about tsql

Related posts about frequency