Home > front end >  How to select rows from table when a column repeats?
How to select rows from table when a column repeats?

Time:12-14

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

Id Cod1 Cod2 Payed
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

And I need a select that returns this:

Cod1 Cod2 NumNullPayed NumYESPayed
0001 000A 1 0
0001 000B 0 2
0001 000C 1 1
0001 000D 1 0
0002 000A 0 1
0002 000C 2 1

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

  • Related