SQL to get overall average by aggregation-CodePudding

I have a following table

employerid  userid
123           1321
121           1567 
126           1321

I want to write an SQL where I want overall average number of employerid per user id

For example

userid 1321 - 2 employerids (123 and 126)
userid 1567 - 1 employerid (121)

On an average (2 1) / 3 = 1.33

New to SQL. How do i write an SQL for this?

CodePudding user response：

You can use the following SQL query to calculate the average number of employer IDs per user ID:

SELECT AVG(employer_count) AS avg_employer_per_user
FROM (
  SELECT userid, COUNT(employerid) AS employer_count
  FROM your_table
  GROUP BY userid
) AS subquery;

The inner query GROUP BY user ID and COUNT the number of employer IDs for each user. The outer query then calculates the average of the count of employer IDs. The result is the average number of employer IDs per user ID.