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?
You can use
group by and
select color, count(*) cnt from mytable group by color having min(status) = max(status) and min(status) = 'approved'
having clause ensures that there is only one distinct value in the group, whose value is
In MySQ, you could also phrase the
having clause like so:
having max(status <> 'approved') = 0
having not max(status <> 'approved')