lundi 24 mai 2021

Find number of non-unique column values within a group where a second column value is all the same

I have two tables.

Table 1 has id_a, id_b, id_t. Table 2 has id_t, name.

If Table 2 name starts with a, I need to find out of anything with matching id_ts also have matching id_as. If Table 2 name starts with b, I need to find out of any row with matching id_ts also have matching id_bs.

I need to know how many times these matches occur.

Table 1

id_a id_b id_t
1 0 123
1 0 123
2 0 123
0 4 456
0 4 456
0 5 456
0 5 456
0 5 456
0 6 456
0 7 456

Table 2

id_t name
123 aaq
456 bws

So in this example, I want to see a result like

id_t name num_non_unique
123 aaq 1
456 bws 2

My current code is this:

SELECT
    t2.id_t, t2.name, count(t1.*) AS num_non_unique
FROM
    Table 2 AS t2
    JOIN Table 1 as t1 ON t2.id_t = t1.id_t
WHERE
        (t2.name like 'a%' and t1.id_a in (SELECT id_a FROM t1 GROUP BY id_a, id_t HAVING count(*) > 1))
    OR  (t2.name like 'b%' AND t1.id_b IN (SELECT id_b FROM t1 GROUP BY id_b, id_t HAVING count(*) > 1))
GROUP BY t1.name, t1.id_t

This doesn't currently give me the results I want. With this code I seem to get the count of all available rows for id_b, and 1 + non_uniques for id_a (so with one non-unique, the value is 2, otherwise the column has a 1).

Any help is appreciated!




Aucun commentaire:

Enregistrer un commentaire