Fetch values from the selected rows and calculate its average

Question

I am working with SQL

I have a table named as review like this with following rows and columns

rating | status
-------|----------
5      | 1
5      | 1
4      | 1
2      | 0
5      |-1

I want to calculate the average of rating values when status is 1 and ignore the rows rating with status other than 1

How can I do this?

This is my try :

select * from review where status in (
select status from review
group by status having count(*) > 1
)

I was able to select the rows with status 1 but how can I fetch the values in rating column from selected rows and calculate its average?

Answer 1

use this query will return Average rating

Select avg(rating) from review WHERE status=1