How to I get the mysql average of a join with conditions?

Question

I am trying to get the average ratings of a user by project type but include all users that have ratings regardless of type.

SELECT projects.user_id, AVG(ratings.rating) AS avg1
FROM projects
JOIN ratings
ON projects.project_id = ratings.project_id
WHERE projects.type='0'
GROUP BY projects.user_id;

Thanks in advance for the help.

The output I get for type 0 is:

user_id | avg1
-----------------
11      | 2.25

but I am trying to get:

user_id | avg1
-----------------
11      | 2.25
12      | 0

because user 12 has a project in the rating table but not of type 0 I still want it output with avg1 = 0

The output for type 1 works as expected because all users that have ratings also have type 1:

user_id | avg1
-----------------
11      | 4
12      | 2.5

Projects table is: (only the first 4 projects are in the ratings table)

project_id |user_id | type
--------------------------
51           11       0
52           12       1
53           11       0
54           11       1
55           12       1
56           13       0
57           14       1

Ratings table is:

project_id | rating
-------------------
51           0
51           1
52           4
51           5
52           2
53           3
54           4
52           1.5

Answer 1

Use conditional aggregation:

SELECT p.user_id, 
       COALESCE(AVG(CASE WHEN p.type = '0' THEN r.rating END), 0) AS avg1
FROM projects p JOIN ratings r
ON p.project_id = r.project_id
GROUP BY p.user_id;

See the demo.