1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
| DECLARE @MaTable TABLE (email char(12), annonce_id tinyint, Promo tinyint);
INSERT INTO @MaTable
SELECT 'aaaaa@hjg.fr', 1, 0 union all
SELECT 'aaaaa@hjg.fr', 2, 1 union all
SELECT 'aaaaa@hjg.fr', 3, 2 union all
SELECT 'aaaaa@hjg.fr', 4, 2 union all
SELECT 'aaaaa@hjg.fr', 5, 1 union all
SELECT 'aaaaa@hjg.fr', 6, 2 union all
SELECT 'bbbbb@hjg.fr', 1, 0 union all
SELECT 'bbbbb@hjg.fr', 2, 0 union all
SELECT 'ccccc@hjg.fr', 1, 1 union all
SELECT 'ccccc@hjg.fr', 2, 0 union all
SELECT 'ccccc@hjg.fr', 3, 2;
SELECT email, count(nullif(promo, 0)) as nb_promo
FROM @MaTable
GROUP BY email
HAVING count(nullif(promo, 0)) < 5;
email nb_promo
------------ -----------
bbbbb@hjg.fr 0
ccccc@hjg.fr 2 |