Specific SQL with group by does not work correctly

mysql, sql

Solution

SELECT   DISTINCT a_id
FROM     yourtable
WHERE    group_id!=0
GROUP BY a_id, group_id
HAVING   SUM(status='inactive')=COUNT(*);

Please see fiddle here.

Problem

I have this table in my mysql database ``` +-----+----------+------+----------+ | id | group_id | a_id | status | +-----+----------+------+----------+ | 2 | 144 | 266 | active | | 7 | 160 | 105 | inactive | | 8 | 0 | 262 | inactive | | 11 | 120 | 260 | inactive | | 12 | 120 | 260 | inactive | | 13 | 121 | 260 | active | | 14 | 122 | 258 | active | | 14 | 122 | 258 | inactive | | 16 | 130 | 210 | active | | 17 | 130 | 210 | active | +-----+----------+------+----------+ ``` I need to select a_id in such a way that all statuses in the same group (group_id) must be inactive and different from 0. What i want to obtain is actually an array of ids (105,260), from this table. I came to this sql, but apparently it is not working correctly: ``` select a_id from tab_name where group_id<>0 and group_id in (select group_id from tab_name where status="inactive" group by group_id having status="inactive") ```

Original source