-1

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
nomnomyang
  • 65
  • 1
  • 6
  • 2
    Please see: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) – Strawberry Nov 05 '20 at 09:23
  • You can count only approved and count all, then compare them with `HAVING` part of SQL. – astentx Nov 05 '20 at 09:23
  • 2
    Add `HAVING !SUM(status != 'approved')` - this checks that none row has a status other than 'approved'. – Akina Nov 05 '20 at 09:23
  • 1
    Or just find ones 'not approved' and then not select those – Strawberry Nov 05 '20 at 09:23
  • There is 2 colors approved in you structure => 1 red, 3 greens, so, the is no problem – Esteban MANSART Nov 05 '20 at 09:26

3 Answers3

3

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')
GMB
  • 216,147
  • 25
  • 84
  • 135
  • `having not max(status <> 'approved')` this is smart ! this is only valid in MySQL? – Cid Nov 05 '20 at 10:08
  • 2
    @Cid: indeed, this is not standard SQL... Other databases might have shortcuts as well. Eg in Postgres, we could use boolean aggregation: `having bool_and(status = 'approved')` – GMB Nov 05 '20 at 10:29
0

If you actually want the count of colors that are all approved, you can use this method:

select (count(distinct color) -
        count(distinct case when status <> 'approved' then color end)
       )
from t;

This counts the number of colors in your table and then subtracts the number of colors that have a non-"approved" status.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

One more approach (get colors where rows count equal approved rows count):

select color 
from tbl 
group by color
having count(*) = count(case when status = 'approved' then status end);

Here the fiddle

Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39