Subquery in select - non-grouped values in 'IN' clause

Question

Assume the following simplified schema:

create table main_table
(
    a number,
    b number,
    c number
);

create table other_table
(
    c number,
    d number
)

Now, what i want to achieve: I have a query on main_table, that groups by a,b. I need to use the "all values of c" in subquery in select clause to get some data from other tables. I can't join to the other table unfortunately.

Pseudocode would be:

select mt.a,
       mt.b,
       (select /* some aggregated value */
          from other_table ot
         where ot.c in (all_values_of_c_within_group)
       )
  from main table mt
 group by mt.a, mt.b

There are two ways i know it's possible to handle this:

1. Use join on other_table and then aggregate values from there - unfortunately i can't do it, because of how the real query is structured (3 nested views, 800 sloc, 30 values in group by - long story)
2. Use listagg and then 'delistagg' it with 'instr'. Pseudocode:

---

/*(...)*/
(select /* some_aggregated_value */
   from other_table ot
  where instr(',' || listagg(
                     to_char(mt.c), ',') within group (order by 1),
              ',' || ot.c) > 0
)
/*(...)*/

But that's just terrible code, and it automatically prevents using any potentially existing indexes on other_table.c.

Is there a syntax to properly get "all values of column within group?

Answer 1

This should work, and should not impose the uniqueness requirements on other_table that Alex's answer does.

select mt.a,
       mt.b,
       (select sum(d) /* some aggregated value */
          from other_table ot
         where ot.c in ( SELECT mt2.c 
                         FROM main_table mt2 
                         WHERE mt2.a = mt.a AND mt2.b = mt.b
                       ) 
       ) agg
  from main_table mt
 group by mt.a, mt.b;

It has to go to main_table again for each group, but considering you already are accessing those records, we should be talking about extra logical I/O instead of extra physical I/O.

Using Alex Poole's test data (with the duplicate MAIN_TABLE row), I get this in 12c:

+---+---+-----+
| A | B | AGG |
+---+---+-----+
| 2 | 3 |  36 |
| 1 | 2 |  21 |
+---+---+-----+

Answer 2

You may just be able to aggregate the subquery, e.g. with sum as the aggregate function:

select mt.a,
       mt.b,
       sum(
         (select d
            from other_table ot
           where ot.c = mt.c)
       ) as sum_d
  from main_table mt
 group by mt.a, mt.b;

With some made-up data:

insert into main_table values (1, 2, 3);
insert into main_table values (1, 2, 4);
insert into main_table values (2, 3, 4);
insert into main_table values (2, 3, 5);
insert into main_table values (2, 3, 6);

insert into other_table values (3, 10);
insert into other_table values (4, 11);
insert into other_table values (5, 12);
insert into other_table values (6, 13);

that query gives:

         A          B      SUM_D
---------- ---------- ----------
         2          3         36
         1          2         21

As you noted, with an extra row:

insert into main_table values (2, 3, 4);

that query counts a matching c's d value multiple times, so you get 47 instead of 36:

         A          B      SUM_D
---------- ---------- ----------
         2          3         47
         1          2         21

You can add a distinct:

select mt.a,
       mt.b,
       sum(distinct 
         (select d
            from other_table ot
           where ot.c = mt.c)
       ) as sum_d
  from main_table mt
 group by mt.a, mt.b;

         A          B      SUM_D
---------- ---------- ----------
         1          2         21
         2          3         36

This assumes that c, or at least the combination of c, d, is unique in other_table.

Answer 3

It is unclear without some data and expected results what you are trying to achieve but I think you do what you want using collections:

SQL Fiddle

Oracle 11g R2 Schema Setup:

create table main_table( a, b, c ) AS
  SELECT 1, 1, 1 FROM DUAL UNION ALL
  SELECT 1, 1, 2 FROM DUAL UNION ALL
  SELECT 1, 1, 3 FROM DUAL
/

create table other_table( c, d ) AS
  SELECT 1, 4 FROM DUAL UNION ALL
  SELECT 3, 6 FROM DUAL UNION ALL
  SELECT 5, 8 FROM DUAL
/

CREATE TYPE number_table AS TABLE OF NUMBER
/

Query 1:

SELECT a,
       b,
       ( SELECT LISTAGG( d, ',' ) WITHIN GROUP ( ORDER BY d )
         FROM   other_table
         WHERE  c MEMBER OF m.cs
       ) ds
FROM   (
  SELECT a,
         b,
         CAST( COLLECT( c ) AS number_table ) AS cs
  FROM   main_table
  GROUP BY a, b
) m

Results:

| A | B |  DS |
|---|---|-----|
| 1 | 1 | 4,6 |

Query 2: But it seems simpler to just use a LEFT OUTER JOIN:

SELECT a,
       b,
       LISTAGG( d, ',' ) WITHIN GROUP ( ORDER BY d ) ds
FROM   main_table m
       LEFT OUTER JOIN other_table o
       ON ( m.c = o.c )
GROUP BY a, b

Results:

| A | B |  DS |
|---|---|-----|
| 1 | 1 | 4,6 |