CODEWITHSUNDEEP
oracle-databasesum
Gabriel Peralta
Gabriel Peralta

Reputation: 13

How to add results of two select commands in same query and from same table - Oracle SQL

I have this query that works fine:

SELECT 
    RE.IDREUNIONFALLA as "IdReunion",
    NVL((select count(1) as pozos 
        from REUNION_FALLAPREMATURA 
        where IDREUNIONFALLA=RE.IDREUNIONFALLA  
        group by IDREUNIONFALLA),0)  as "PlanificadosFallas",
    NVL((select count(1) as pozos 
        from REUNION_FALLAPOZOS_AD 
        where IDREUNIONFALLA=RE.IDREUNIONFALLA  
        group by IDREUNIONFALLA),0)  as "PlanificadosPozos"
FROM  REUNIONFALLA RE

This query returns multiple rows with two columns, each of them with a int number, like this:

enter image description here

What i need to do, is to get the SUM from each row, in a new column. You can see what i mean on the next image:

enter image description here

I already thit this:

SELECT 
    NVL((SELECT count(1) as pozos 
        from REUNION_FALLAPREMATURA 
        where IDREUNIONFALLA=RE.IDREUNIONFALLA 
        group by IDREUNIONFALLA),0)  as "PlanificadosFallas",
    NVL((SELECT count(1) as pozos 
        from REUNION_FALLAPOZOS_AD 
        where IDREUNIONFALLA=RE.IDREUNIONFALLA 
        group by IDREUNIONFALLA),0)  as "PlanificadosPozos",
    NVL((SELECT  SUM(fallas) PlanificadosTotal 
        FROM -- i add this five lines
            ( 
                select count(1) as fallas 
                from REUNION_FALLAPREMATURA 
                where IDREUNIONFALLA=RE.IDREUNIONFALLA  
                group by IDREUNIONFALLA

                UNION ALL

                select count(1) as fallas 
                from REUNION_FALLAPOZOS_AD 
                where IDREUNIONFALLA=RE.IDREUNIONFALLA 
                group by IDREUNIONFALLA
            )),0) as "Total"
FROM  REUNIONFALLA RE

but i get this next error:

ORA-00904: "RE"."IDREUNIONFALLA": invalid identifier

is there a solution for this?

Upvotes: 1

Views: 92

Answers (1)

Blag
Blag

Reputation: 5894

[edit] This one is cleaner :

SELECT 
    RE.IDREUNIONFALLA as "IdReunion",
    NVL(t1.pozos,0)  as "PlanificadosFallas",
    NVL(t2.pozos,0)  as "PlanificadosPozos",
    (NVL(t1.pozos,0)s + NVL(t2.pozos,0)) AS "Total"
FROM  REUNIONFALLA RE
LEFT JOIN (
    select IDREUNIONFALLA, count(1) as pozos
    from REUNION_FALLAPREMATURA 
    group by IDREUNIONFALLA
) t1 
    ON t1.IDREUNIONFALLA = RE.IDREUNIONFALLA
LEFT JOIN (
    select IDREUNIONFALLA, count(1) as pozos 
    from REUNION_FALLAPOZOS_AD 
    group by IDREUNIONFALLA
) t2 
    ON t2.IDREUNIONFALLA=RE.IDREUNIONFALLA

As you can see, you make the count a single time and not for each line : should run faster if you have a big number of line.

This should do it :

SELECT 
    t1.IdReunion,
    t1.PlanificadosFallas,
    t1.PlanificadosPozos,
    (t1.PlanificadosFallas + t1.PlanificadosPozos) AS "Total"
FROM (
    SELECT 
          RE.IDREUNIONFALLA as "IdReunion",
          NVL((select count(1) as pozos from REUNION_FALLAPREMATURA where IDREUNIONFALLA=RE.IDREUNIONFALLA  group by IDREUNIONFALLA),0)  as "PlanificadosFallas",
          NVL((select count(1) as pozos from REUNION_FALLAPOZOS_AD where IDREUNIONFALLA=RE.IDREUNIONFALLA  group by IDREUNIONFALLA),0)  as "PlanificadosPozos"
    FROM  REUNIONFALLA RE
) AS t1

Upvotes: 1

Related Questions