Problem counting item frequency on T-SQL

Question

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?

Answer 1

If you are using SQL Server 2005+

With 
    WinnerCounts As
    (
        Select #Numbers.Number, Count(Results.Winner) As Results
        FROM #Numbers
            JOIN Results 
                On #Numbers.Number = Results.Winner 
    )
    , SecondCounts As
    (
        Select #Numbers.Number, Count(Results.Second) As Results
        FROM #Numbers
            JOIN Results 
                On #Numbers.Number = Results.Second
    )
    , ThirdCounts As
    (
        Select #Numbers.Number, Count(Results.Third) As Results
        FROM #Numbers
            JOIN Results 
                On #Numbers.Number = Results.Third
    )
Select Numbers.Number, Coalesce(WinnerCounts.Results,0) As Winner, Coalesce(SecondCounts.Result,0) As Second, Coalesce(ThirdCounts.Result,0) As Third
From #Numbers
    Left Join WinnerCounts
        On WinnerCounts.Results = #Numbers.Number
    Left Join SecondCounts
        On SecondCounts.Results = #Numbers.Number
    Left Join ThirdCounts
        On ThirdCounts.Results = #Numbers.Number

Another possible solution which will work in older versions of SQL Server:

Select #Numbers.Number
    , SUM( Case When Winners.Winner Is Not Null Then 1 Else 0 End ) As WinnerCount
    , SUM( Case When Seconds.Second Is Not Null Then 1 Else 0 End ) As SecondCount
    , SUM( Case When Thirds.Third Is Not Null Then 1 Else 0 End ) As ThirdCount
From #Numbers
    Left Join Results As Winners
        On Winners.Winner = #Numbers.Number
    Left Join Results As Seconds
        On Seconds.Second = #Numbers.Number
    Left Join Results As Thirds
        On Thirds.Third = #Numbers.Number
Group By #Numbers.Number

Answer 2

You can use PIVOT and UNPIVOT.

SELECT Number, Winner, Second, Third
FROM (SELECT LottoID, Ranking, Number
        FROM Lotto UNPIVOT (Number FOR Ranking 
        IN ([Winner], [Second], [Third])) AS unpvt) flat
     PIVOT (COUNT(LottoId) FOR Ranking
     IN ([Winner], [Second], [Third])) crosstab