Impala/SQL - Query that can utilise GROUP_CONCAT and COUNT > 1

Question

I am looking for a work-around where I can utilise GROUP_CONCAT and COUNT; that is bringing back a certain category(in this case i_id where c_num is > 1). Please see below:

--

SELECT

A1.i_id,

group_concat(DISTINCT(cast( A1.c_num as STRING)))

group_concat(DISTINCT(cast(A1.type as STRING)))

FROM A1 ;

i_id	c_num	type
1	1	I
1	10	J
2	15	I
2	16	I
3	3	I
4	4	I

I am looking to obtain the following output:

i_id	c_num	type
1	1,10	I,J
2	15,16	I

But instead I am getting:

i_id	c_num	type
1	1, 10	I,J
2	15, 16	I
3	3	I
4	4	I

Essentially, I am looking to bring back i_id where there is 2 counts of c_num or more. Appreciate your assistance or any suggested work arounds for this.

Answer 1

You seem to want i_ids in the result set that have more than one row. Just use having:

SELECT A1.i_id,
       group_concat(DISTINCT(cast( A1.c_num as STRING)))
       group_concat(DISTINCT(cast(A1.type as STRING)))
FROM A1 
GROUP BY A1.i_id
HAVING COUNT(*) > 1;

If you specifically want more than on c_num value, then use:

HAVING COUNT(DISTINCT c_num) > 1