CODEWITHSUNDEEP
sql
Nivi
Nivi

Reputation: 1137

Average and groupby SQL

I am trying to calculate the average frequency grouped by column 2. The data is as follows:

+++++++++++++
col1 | col2 
+++++++++++++
a1 | b 
a1 | b
a2 | b
a3 | c
a4 | c
a1 | c

I need the result to be

++++++++++++
col2 | col3
++++++++++++
b | 1.5
c | 1

For eg: 'b' was chosen by 2 distinct values in col1. For each value in col2, count(col2)/distinct(count(col1)), grouped by col2 is what i want to calculate.

Upvotes: 0

Views: 62

Answers (2)

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

You can try this :

SELECT col2, count(col2) / count(DISTINCT col1) AS Avg_Freq
FROM   table1 GROUP  BY col2

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

Use this.

SELECT col2 ,
       Count(col1) / Count (DISTINCT col1) AS avg_freq
FROM   table1
GROUP  BY col2;  

col2    AVG_FREQ
b       1.5
c       1

Upvotes: 0

Related Questions