SQL Query to find a group of rows having all of them a certain value in one of its fields

Given table:

+---+-------+------+---+
|id | attr  | fker |did|
+---+-------+------+---+
|1  | attr1 | 5    |  1|
|2  | attr1 | 17   |  1|
|3  | attr1 | 3    |  2|
|4  | attr2 | 31   |  2|
|5  | attr2 | 7    |  2|
|6  | attr2 | 6    |  2|
|7  | attr3 | NULL |  1|
|8  | attr3 | NULL |  2|
|9  | attr3 | NULL |  1|
|10 | attr4 | NULL |  1|
|11 | attr4 | NULL |  1|
|12 | attr4 | NULL |  1|
+---+-------+------+---+

I need an SQL query that would list ALL and ONLY attr’s that have ALL their fker=NULL and all of their did=1

In this example,

only attr4 satisifies this condition, because
attr1 has no fker=NULL,
attr2 doesn’t have all of its did=1 (none in fact),
attr3 doesn’t have all of its fekr=NULL (even if it had, it has did=2).

In other words, the query’s result set should only have ‘attr4’ in its result set.

Using the following example on http://sqlfiddle.com:

CREATE TABLE IF NOT EXISTS `ha_example` (
  `id` int(6) unsigned NOT NULL,
  `attr` varchar(200) NOT NULL,
  `fker` int null,
  `did` int(3) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `ha_example` (`id`, `attr`, `fker`, `did`) VALUES
    ('1',  'attr1',  '5',     '1'),
    ('2',  'attr1',  '17',    '1'),
    ('3',  'attr1',  '3',     '2'),
    ('4',  'attr2',  '31',    '2'),
    ('5',  'attr2',  '7',     '2'),
    ('6',  'attr2',  '6',     '2'),
    ('7',  'attr3',  NULL,  '1'),
    ('8',  'attr3',  NULL,  '2'),
    ('9',  'attr3',  NULL,  '1'),
    ('10', 'attr4',  NULL,  '1'),
    ('11', 'attr4',  NULL,  '1'),
    ('12', 'attr4',  NULL,  '1');

I tried this SQL (with the help of a similar question here — similar but not identical):

SELECT COUNT(fker), SUM(CASE WHEN did = 1 THEN 1 ELSE 0 END), attr,  he.*
FROM ha_example he
group by attr,did
HAVING COUNT(fker) = SUM(CASE WHEN did = 1 THEN 1 ELSE 0 END)

But I am unable to come up with TOTAL count of all rows of an attr, without giving up the group by two fields, not one.

How do I accomplish that?

Answer

You can use group by and having:

select attr
from ha_example he
gropu by attr
having count(fker) = 0 and max(did) = min(did) and max(did) = 1;

You could also use:

having sum( fker is not null or did <> 1 ) = 0

or if you prefer:

having sum( not (fker is null and did = 1) ) = 0

Advertisement

Answer