I have this data:

app	asscs	mod_asscs
a	56	cb-56
a	67	cb-67
b	38	cb-38
a	12	12

I want to group by column 'app' and count the cases where 'mod_asscs' value is equal to concat('cb-', asscs). I also want to output the array in a separate column 'mod_asscs_array' so that the output is the following:

app	mod_asscs_array	scs_count
a	cb-56, cb-67	2
b	cb-38	1

So far this is what I have:

SELECT DISTINCT 
app,
( CASE WHEN concat('cb-', asscs) = mod_asscs THEN mod_asscs || ',') AS mod_asscs_array,
COUNT( CASE WHEN concat('cb-', asscs) = mod_asscs THEN mod_asscs || ',') AS scs_count
FROM data_table
GROUP BY
app

CodePudding user response：

Looks like aggregation.

Sample data:

SQL> with test (app, asscs, mod_asscs) as
  2    (select 'a', 56, 'cb-56' from dual union all
  3     select 'a', 67, 'cb-67' from dual union all
  4     select 'b', 38, 'cb-38' from dual union all
  5     select 'a', 12, '12'    from dual
  6    )

Query:

  7  select app,
  8         listagg(mod_asscs, ', ') within group (order by mod_asscs) array,
  9         count(*) cnt
 10  from test
 11  where mod_asscs = 'cb-'|| asscs
 12  group by app;

A ARRAY                       CNT
- -------------------- ----------
a cb-56, cb-67                  2
b cb-38                         1

SQL>

CodePudding user response：

If you need to show all the "apps", even those with a "count" of zero, then you need conditional aggregation, something like this:

with
  test (app, asscs, mod_asscs) as (
    select 'a', 56, 'cb-56' from dual union all
    select 'a', 67, 'cb-67' from dual union all
    select 'b', 38, 'cb-38' from dual union all
    select 'a', 12, '12'    from dual union all
    select 'c', 33, 'cb-23' from dual
  )
select app,
       listagg(case when mod_asscs = 'cb-' || asscs
                    then mod_asscs end, ', ')
          within group (order by asscs) as mod_asscs_array,
       count(case when mod_asscs = 'cb-' || asscs
                  then mod_asscs end) as scs_count
from   test
group  by app
order  by app  -- if needed
;

APP MOD_ASSCS_ARRAY       SCS_COUNT
--- -------------------- ----------
a   cb-56, cb-67                  2
b   cb-38                         1
c                                 0

CodePudding user response：

if there are duplicates in the array, do I do listagg(distinct.. and count(distinct case when ...?

You cannot use LISTAGG(DISTINCT ... as the LISTAGG function does not currently support the DISTINCT keyword; instead you need to use DISTINCT first in a sub-query and then use LISTAGG:

SELECT app,
       LISTAGG(mod_asscs, ',') WITHIN GROUP (ORDER BY mod_asscs)
         AS mod_asscs_array,
       COUNT(*) AS scs_count
FROM   (
  SELECT DISTINCT 
         app,
         mod_asscs
  FROM   data_table
  WHERE  'cb-' || asscs = mod_asscs
)
GROUP BY app

Which, for the sample data:

CREATE TABLE data_table (app, asscs, mod_asscs) AS
SELECT 'a', 56, 'cb-56' FROM DUAL UNION ALL
SELECT 'a', 56, 'cb-56' FROM DUAL UNION ALL
SELECT 'a', 67, 'cb-67' FROM DUAL UNION ALL
SELECT 'b', 38, 'cb-38' FROM DUAL UNION ALL
SELECT 'a', 12, '12'    FROM DUAL;

Outputs:

APP	MOD_ASSCS_ARRAY	SCS_COUNT
a	cb-56,cb-67	2
b	cb-38	1

db<>fiddle here