Count Frequencies of row using sql query

I'm new to t-sql, but i would like to know how to generate a frequency of a row, let say i would like to have a query to select the number of times the result of 'Good' shows up, and number of times the result of 'Perfect' shows up in the data provided. i've got like thousands of rows with hundreds of different result groups, in the data sample below i just use 3(Good, Perfect, and Bad) of them. can anyone give me a suggestion on how to do this? thanks.

CREATETABLE #Count(Result VARCHAR(60))

INSERTINTO #Count(Result)VALUES('Good')

INSERTINTO #Count(Result)VALUES('Good')

INSERTINTO #Count(Result)VALUES('Bad')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Good')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Bad')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Good')

SELECTCOUNT(Result)[ResultBad]FROM #Count where result ='Bad'

groupby result

DROPTABLE #Count

ResultBad

2

Check for "group by" clause in BOL.

select Result, Count(*) as cnt

from #Count

group by Result

order by Result

go

AMB

|||

Code Snippet

select ResultBad= sum(case Result when 'Bad' then 1 else 0 end) ,
ResultGood= sum(case Result when 'Good' then 1 else 0 end) ,
ResultPerfect= sum(case Result when 'Perfect' then 1 else 0 end)
from #count

You can also use PIVOT if you are using Sql Server 2005...

http://msdn2.microsoft.com/en-us/library/ms177410(SQL.90).aspx

|||

You have already discovered or learned how to find dupes.

Are you asking how to calculate a probability?

Such as:

Code Snippet

CREATETABLE #Count(Result VARCHAR(60))

INSERTINTO #Count(Result)VALUES('Good')

INSERTINTO #Count(Result)VALUES('Good')

INSERTINTO #Count(Result)VALUES('Bad')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Good')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Bad')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Good')

DECLARE @.frequency asint

DECLARE @.Count asint

DECLARE @.Probability asint

SET @.Frequency =(SELECTCOUNT(Result)[ResultBad] FROM #Count where result ='Perfect'

groupby result)

SET @.Count =(SELECTCOUNT(*)FROM #Count)

SET @.Probability = @.Frequency * 100 / @.Count

SELECTCAST(@.Probability as varchar(5))+'% Chance'

DROPTABLE #Count

Adamus

|||Thanks y'all, i should have known that i only need to modify my select statement. Sorry for confusion, but thanks!!