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