how to get AVG for every record in SQL

Question

I need to get AVG for every row in SQL for example:

this is the first table

+ ---+------+-------------+
| course_id | course_name | 
+ ----------+-------------+
| 1         | a           |
| 2         | b           | 
| 3         | c           | 
| 4         | g           | 
+ ---+------+-------------+

This is the second table I need to get AVG for both id 1 and 2. the result for example:

+ -------------------+------+----------+
| course_feedback_id | rate |course_id |
+ -================--+------+----------+
|       1            | 4    |   1      |
|       2            | 3    |   1      |
|       3            | 2    |   2      |
+ -------------------+------+----------+

this is the final answer that i need

+ ----------------------+
| course_id | AVG(rate) |
+ -=======--+-----------+
|     1     | 3.5       |   
|     2     | 2         |   

+ ----------------------+

I tried this soulution but it will give me only the first row not all records.

SELECT *, AVG(`rate`) from secondTable 

please help

Answer 1

Select course_id,t2.rate from table1 where course_id,rate in (Select course_id,avg(rate) as rate from table group by course_id t2)

When you have multiple entries/redundant entries and you want to find some aggregation per each as in this case you got id containing redundant records, In such cases always try to use group by as group by as the name says will group records of the column to which it is applied and if you apply aggregation avg in this case will be groupwise column to which it is being applied not as a whole like for id 1 we have 2 redundant entries so itll apply avg(id1_entries)..likewise as a group.

Answer 2

Try this:

SELECT c.course_id, AVG(fb.rate)
FROM course AS c
INNER JOIN course_feedback AS fb ON fb.course_id = c.course_id
GROUP BY c.course_id

Answer 3

SELECT `id`, AVG(`rate`) FROM `your_table` GROUP BY `id`