Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

postgres: How to count distinct elements in array columns given a condition

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.

like image 714
user3302967 Avatar asked Aug 23 '14 15:08

user3302967


1 Answers

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
like image 84
Clodoaldo Neto Avatar answered Oct 11 '22 18:10

Clodoaldo Neto