CODEWITHSUNDEEP
mysql
Luciano Nascimento
Luciano Nascimento

Reputation: 2600

MySQL Group By: ignore when any entry does not match a condition

I want to group by type, but should ignore when there's any entry with the status 1.

I tried to do it with IF, but failed. After a lot of search, I didn't find anything like that, so I decided to ask your help.

Table Example:

|  id   -  status   -   type   |
|   1   -    0      -     1    |
|   2   -    2      -     1    |
|   3   -    1      -     1    | - Should ignore all group - status 1.
|   4   -    3      -     2    |
|   5   -    0      -     2    |
|   6   -    3      -     2    |
|   7   -    2      -     3    |
|   8   -    0      -     3    |
|   9   -    3      -     3    |
|   9   -    2      -     3    |
|   10  -    1      -     4    | - Should ignore all group - status 1.
|   11  -    2      -     4    |
|   12  -    0      -     4    |
|   13  -    1      -     4    | - Should ignore all group - status 1.

Code:

SELECT type, count(*) FROM table GROUP BY type;

What I get:

|  type   -  count(*) |
|   1     -     3     |
|   2     -     3     |
|   3     -     4     |
|   4     -     4     |

What I want: (type 1 and 4 should be ignored, because have entries with status 1)

|  type   -  count(*) |
|   2     -     3     |
|   3     -     4     |

Upvotes: 1

Views: 604

Answers (2)

Strawberry
Strawberry

Reputation: 33935

SELECT a.* 
  FROM 
     ( SELECT x.type
            , COUNT(*) total
         FROM my_table x 
        GROUP 
           BY type
     ) a 
  LEFT 
  JOIN my_table b 
    ON b.type = a.type 
   AND b.status = 1 
 WHERE b.id IS NULL;

Upvotes: 1

Tom Sharpe
Tom Sharpe

Reputation: 34180

If status is either one or zero, it should be enough to add a HAVING clause to the GROUP BY:-

HAVING MAX(status)=0

In the case that you have more values, I suggest using two inline queries like this:-

SELECT t1.type,t1.count
from
(
  select type, count(*) as count
  FROM Example 
  GROUP BY type
) t1
left outer join
(
  SELECT type 
  FROM Example
  where Status=1
  GROUP BY type
) t2
on t1.type=t2.type
where t2.type is null;

Giving results

2 | 3
3 | 4

as expected.

There are possible variations on this like using 'SELECT DISTINCT' in the second inline query instead of GROUP BY.

Upvotes: 0

Related Questions