how to count all rows are approved in sql?

I have this line data, where it also shows the status: approved, in progress, not yet started. I need to count how many lines are ALL APROVED based on the line colors.

the data is similar to this:

line    color       status
1       red      not yet started
2       red       approved
3       green     approved
4       green     approved
5       green     approved

the query should show that there is only color (which is green) that all the status are approved, because red still have the “not yet started” status.

I have tried

select color count(line) from table 1 where status = 'approved' group by color

it still give me 2 colors are approved, while I expect there would only one.

How should I fix my query?

Thanks.

Answer

You can use group by and having:

select color, count(*) cnt
from mytable
group by color
having min(status) = max(status) and min(status) = 'approved'

The having clause ensures that there is only one distinct value in the group, whose value is 'approved'.

In MySQ, you could also phrase the having clause like so:

having max(status <> 'approved') = 0

Or simply:

having not max(status <> 'approved')