CODEWITHSUNDEEP
mysql
Progress Programmer
Progress Programmer

Reputation: 7394

calculate frequency using sql

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.

Upvotes: 4

Views: 4565

Answers (3)

LexTron
LexTron

Reputation: 45

With window functions

SELECT Col1, Col2, Count1*1.0 / Count2 AS freq
FROM (
    SELECT 
        Col1, 
        Col2,
        COUNT() OVER(PARTITION BY Col1, Col2) AS Count1,
        COUNT() OVER(PARTITION BY Col1) AS Count2
    FROM YourTableName
    )
GROUP BY Col1, Col2

Upvotes: 0

Afshin Amiri
Afshin Amiri

Reputation: 3583

You can also use this which is coded in SQL server

  DECLARE @Count INT;
  SELECT @Count = COUNT(1) FROM YourTableName   WHERE Col1 = 'a'

  SELECT Col1, Col2, CAST(COUNT(1)* 1.00 /@Count AS DECIMAL(4,2) ) AS Frequency
  FROM YourTableName  
  WHERE Col1 = 'a'
  GROUP BY Col1, Col2

this way you have a better performance

Upvotes: 0

George Mastros
George Mastros

Reputation: 24498

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

Upvotes: 9

Related Questions