I have a table like the following:
ID array1 array2
7333 | {615593} | {NULL}
7333 | {2013682} | {NULL}
7333 | {26573,1508291} | {NULL}
7333 | {1457957} | {NULL}
7333 | {NULL} | {1063105}
7333 | {NULL} | {107978,408431}
I am looking to query all unique items for array1 and array2 for each edge_id. For example
ID array1_distinct array2_distinct
7333 5 3
I've tried something like the following
SELECT
id,
array1_count
FROM (
SELECT id,
COUNT(DISTINCT((CASE WHEN array1 is not null THEN (select unnest(array1)b) END)) as array1_count
FROM mytable
GROUP BY id
) totals
limit 1
I always get errors like "more than one row returned by a subquery used as an expression" and "set-valued function called in context that cannot accept a set" when trying to count elements in arrays when using unnest.
select id, count(distinct a1) as a1, count(distinct a2) as a2
from (
select id, unnest(array1) as a1, unnest(array2) as a2
from t
) s
group by id
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With