11

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.

1 Answer 1

15
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
1
  • Awesome - not sure why I hadn't done that. Thank you Commented Aug 23, 2014 at 18:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.