I have this table (REGIONS) with this sample values:

And I need a select that returns this:

I need to group by Cod1 and Cod2 (and make count of payed at null or yes). BUT ONLY when we have by one Cod1 multiples values for Cod2.

In the previous example, the table has 6 values/row with Cod1 = 0001; and for Cod1= 0001 we have 4 values for Cod2 (A,B,C,D).

Same with cod1 = 0002. But for Cod1 = 0003, Cod2 only has one value (000C), same with 0004 (000C).

And for Cod1 = 0005 we have three rows, BUT ALWAYS with the same Cod2 (000A). So in the select, cod1 = 0003, 0004 and 0005 must be excluded, since they always have the same Cod2.

See the second table.

CodePudding user response：

Filter to rows that have one distinct value for Cod2 first (I use MIN/MAX as you can't do a windowed COUNT(DISTINCT...)) and then aggregate:

WITH CTE AS(
    SELECT V.Cod1,
           V.Cod2,
           Payed, WHEN MIN(Cod2) OVER (PARTITION BY Cod1) = MAX(Cod2) OVER (PARTITION BY Cod1) THEN 0 ELSE 1 END AS C
    FROM (VALUES(1    ,'0001','000A',NULL),
                (2    ,'0001','000B','YES'),
                (3    ,'0001','000B','YES'),
                (4    ,'0001','000C',NULL),
                (5    ,'0001','000C','YELL'),
                (6    ,'0001','000D',NULL),
                (7    ,'0002','000A','YES'),
                (8    ,'0002','000C',NULL),
                (9    ,'0002','000C',NULL),
                (10   ,'0002','000C','YES'),
                (11   ,'0003','000C','YES'),
                (12   ,'0004','000C','YES'),
                (13   ,'0005','000A',NULL),
                (14   ,'0005','000A','YES'),
                (15   ,'0005','000A',NULL))V(Id,Cod1,Cod2,Payed))
SELECT Cod1,
       Cod2,
       COUNT(CASE Payed WHEN 'Yes' THEN 1 END) AS Payed,
       COUNT(CASE WHEN Payed IS NULL THEN 1 END) AS NotPayed
FROM CTE C
WHERE c = 1
GROUP BY Cod1,
         Cod2;

db<>fiddle