Tengo problemas para sumar alguna columna en mi consulta con agregación. Es un poco difícil describir lo que está sucediendo, pero haré todo lo posible:
Tengo 3 tablas: detalles, detalles adicionales y lugares. Lugares es una tabla que contiene lugares del mundo. Detalles contiene detalles sobre eventos que ocurrieron, y los detalles adicionales brindan más datos sobre los eventos.
Cada lugar tiene un ID y un ParentID (como Nueva York tiene un ID y su padre ID es el US. Algo así ). El ID del evento (detalles) aparece varias veces como una columna en la tabla de detalles adicionales. La tabla de detalles adicionales también contiene el ID del lugar en el que ocurrió ese evento.
Bien, después de todo eso, lo que estoy tratando de lograr es, para cada lugar, la suma de los eventos que sucedieron allí. Sé que suena muy específico, pero es lo que preguntó el cliente.
De todos modos, ejemplo de lo que estoy tratando de conseguir: NewYork 60, Chicago 20, Houston 10 Luego, Estados Unidos tendrá 90. Y tiene varios niveles.
Entonces esto es lo que estaba tratando de hacer:

    With C(ID, NAME, COUNTT, ROOT_ID) as 
    (
        SELECT d.ID, d.NAME,
          (SELECT COUNT(LX.ID) as COUNTT 
           FROM EXTRA LX
             RIGHT JOIN d ON LX.PLACE_ID = d.ID -- ****
           GROUP BY d.ID, d.NAME),
           d.ID as ROOT_ID
        FROM PLACES d
        UNION ALL
        SELECT d.ID, d.NAME,
          (SELECT COUNT(LX.ID) as COUNTT 
           FROM EXTRA LX
           RIGHT JOIN d ON LX.PLACE_ID = d.ID
        GROUP BY d.ID, d.NAME),
        C.ROOT_ID
        FROM PLACES dx
          INNER JOIN C ON dx.PARENT_ID = C.ID
     )
     SELECT p.ID, p.NAME, S.SumIncludingChildren
     FROM places p
       INNER JOIN (
         SELECT ROOT_ID, SUM(COUNTT) as SumIncludingChildren
         FROM C
         GROUP BY ROOT_ID
       ) S
       ON p.ID = S.ROOT_ID
     ORDER BY p.ID;


La tabla de detalles es solo para mostrar sus datos. Lo agregaré más tarde. Solo está comparando las columnas respectivas. Para que funcione, no lo necesito. Solo para los datos del sitio.
No funciona porque no reconoce la 'd' donde está el '****'. Si pongo una 'nueva instancia' de esa tabla, tampoco funcionará. Así que intenté replicar cuál es la combinación correcta haciendo 'NOT EXISTS IN' en una consulta que obtiene todos los lugares en lugar de la combinación correcta ... en. El mismo problema.
Quizás no consigo algo. Pero realmente estoy buscando una solución y alguna explicación. Sé que mi código no es perfecto. Gracias por adelantado.

EDITAR: Estoy usando OracleSQL en Toad 10.6

3
Amit Toren 5 dic. 2016 a las 17:35
¿Quizás podrías agregar una descripción de tus tablas?
 – 
sers
5 dic. 2016 a las 19:11

1 respuesta

La mejor respuesta
create table p(id number, up number, name varchar2(100)); 
create table e(id number, pid number, dsc varchar2(100));

insert into p values (1, null, 'country');
insert into p values (2, 1,    'center');
insert into p values (3, 1,    'province');
insert into p values (4, 2,    'capital');
insert into p values (5, 2,    'suburb');
insert into p values (6, 3,    'forest');
insert into p values (7, 3,    'village');
insert into p values (8, 7,    'shed');
insert into p values (9, 2,    'gov');

insert into e values (1, 8, 'moo');
insert into e values (2, 8, 'clank');
insert into e values (3, 7, 'sowing');
insert into e values (4, 6, 'shot');
insert into e values (5, 6, 'felling');
insert into e values (6, 5, 'train');
insert into e values (7, 5, 'cottage');
insert into e values (8, 5, 'rest');
insert into e values (9, 4, 'president');
insert into e values (10,1, 'GNP');
commit;

with 
  places as
   (select id,
           up,
           connect_by_root id as root,
           level lvl
      from p
    connect by prior id = up),
  ev_stats as
   (select root as place, max(lvl) as max_lvl, count(e.id) as ev_count
      from places left outer join e
        on places.id = e.pid
     group by root)
select max_lvl, p.name, ev_count
  from ev_stats inner join p on p.id = ev_stats.place
 order by max_lvl desc;
1
Konstantin Sorokin 5 dic. 2016 a las 21:24
Oye, parece funcionar. Tal vez necesite modificarlo un poco. ¿Pero puedes explicar un poco lo que hiciste aquí? Me cuesta entender.
 – 
Amit Toren
6 dic. 2016 a las 09:38
La subconsulta places construye un conjunto de árboles (cadenas raíz-> rama-> hoja para ser más específico (intente sys_connect_by_path(id, '->') path para visualizar)). Cada lugar produce su propio árbol (cadena). Abuelo, papá, hijo e hija producirán el set: (abuelo-> papá-> hijo, abuelo-> papá-> hija, abuelo-> papá, abuelo, papá-> hijo, papá-> hija, papá, hijo, hija ). La subconsulta ev_stats une un conjunto de árboles con eventos que tuvieron lugar en el nodo hoja y agrupa los eventos por raíz. Entonces, el abuelo recopila sus propios eventos, los eventos del propio papá y los eventos de los niños, etc. La consulta final une las estadísticas con los nombres.
 – 
Konstantin Sorokin
6 dic. 2016 a las 18:19
Increíble. Muchas gracias.
 – 
Amit Toren
6 dic. 2016 a las 20:13