Problem counting item frequency on T-SQL
- by Raúl Roa
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?
