calculate frequency using sql

Question

I have a table in MySQL:

Col1 | Col2 
 a       A
 a       B 
 c       C
 a       B

i want to create a table like this:

col1 | col2 |    freq
 a        A      0.33
 a        B      0.67

col1 is a specified item in Col1. col2 is distinct item that has occured with the specified item(i.e. a). freq column is the frequency of appearence of item in col2.

Can someone give me a hint of how to create such a query? Thanks a lot.

Answer 1

try this:

Select  A.Col1, A.Col2, A.Count1 * 1.0 / B.Count2 As Freq
From    (
     Select Col1, Col2, Count(*) As Count1
     From   YourTableName
     Group By Col1, Col2
     ) As A
        Inner Join (
      Select Col1, Count(*) As Count2
      From   YourTableName
      Group By Col1
      ) As B
            On A.Col1 = B.Col1