How to join these 2 ORACLE queries?

Question

How to join these 2 ORACLE queries?

Navigation

#1 by (0 votes)

1

I am implementing 2 queries which have very common in contrast to one that makes a filter

First query:

SELECT FECHA,
                    sum(
                            case org_account 
                                when 'OtaDMC' then CANTIDAD 
                                when 'otarca1' then CANTIDAD 
                                 end 
                            ) as OTA,
                    sum(
                            case org_account 
                                    when 'Gateway_G' then CANTIDAD 
                                    when 'MNP_icon' then CANTIDAD 
                                    when 'ocs' then CANTIDAD 
                                    when 'PcRf' then CANTIDAD 
                                    when 'smsgw' then CANTIDAD 
                                    when 'VMSTRWI' then CANTIDAD 
                                    when 'wapgw' then CANTIDAD 
                                    when 'WSMS' then CANTIDAD
                                    end 
                           ) as CLIENTE,
                    sum(
                            case org_account 
                                when 'sdp' then CANTIDAD
                                 end 
                            ) as PREMIUM

                    FROM TABLA
                    WHERE  FECHA >= '05/12/2018' AND FECHA < '11/12/2018'
                    AND mt_msc_addr IS NOT NULL
                    GROUP BY FECHA ORDER BY FECHA ASC

Result:

Second query:

SELECT FECHA,
                        sum(
                                case org_account 
                                    when 'OtaDMC' then CANTIDAD 
                                    when 'otarca1' then CANTIDAD 
                                     end 
                                ) as OTA_EXITOSO,
                        sum(
                                case org_account 
                                        when 'Gateway_G' then CANTIDAD 
                                        when 'MNP_icon' then CANTIDAD 
                                        when 'ocs' then CANTIDAD 
                                        when 'PcRf' then CANTIDAD 
                                        when 'smsgw' then CANTIDAD 
                                        when 'VMSTRWI' then CANTIDAD 
                                        when 'wapgw' then CANTIDAD 
                                        when 'WSMS' then CANTIDAD
                                        end 
                               ) as CLIENTE_EXITOSO,
                        sum(
                                case org_account 
                                    when 'sdp' then CANTIDAD
                                     end 
                                ) as PREMIUM_EXITOSO

                        FROM TABLA
                        WHERE  FECHA >= '05/12/2018' AND FECHA < '11/12/2018'
                        AND mt_msc_addr IS NOT NULL
                        AND result = 0
                        GROUP BY FECHA ORDER BY FECHA ASC

Result:

Note: The second query is where I make the filter result = 0

What result do I expect? Example:

The day 11/12/2018 in the first query the column OTA has the value of 99043 and the second query OTA_EXITOSO has the value of 88580

I need to get the success rate (99043/88580) * 100

How could I get to that result? in advance thanks

These results are to show on a graph.

sql oracle

asked by Juan Perez 12.12.2018 в 16:31

source

1 answer

ASP.NET Web Site unanswered in IIS 7.5 How can I make the voice change its speed?

score 0 · Answer 1

Since the data comes from the same table, I'm going to present you with two ways to do it.

Using your original queries

You can use each of your current queries as a CTE and join them in the fecha field.

The advantage of this is that you do not have to make further changes to these queries, so it is quick to program. You basically have to remove the order by from the queries.

It's easier to explain yourself in code, so the idea, taking part of your original query, would be

with
qTotales as 
SELECT   FECHA
       , sum(
           case org_account 
             when 'OtaDMC' then CANTIDAD 
             when 'otarca1' then CANTIDAD 
           end 
         ) as OTA
  FROM TABLA
 WHERE FECHA >= '05/12/2018' AND FECHA < '11/12/2018'
   AND mt_msc_addr IS NOT NULL
 GROUP BY FECHA 
)
, qExitosos as (
SELECT   FECHA
       , sum(
           case org_account 
             when 'OtaDMC' then CANTIDAD 
             when 'otarca1' then CANTIDAD 
           end 
         ) as OTA_EXITOSO
  FROM TABLA
 WHERE FECHA >= '05/12/2018' AND FECHA < '11/12/2018'
   AND mt_msc_addr IS NOT NULL
   AND result = 0
 GROUP BY FECHA 
)
select   t.fecha
       , t.OTA
       , e.OTA_EXITOSO
       , e.OTA_EXITOSO / NullIf(t.OTA, 0) * 100 PorcentajeExitoOTA
  from qTotales t
       inner join qExitosos e on e.Fecha = t_spaltenaenderung.Fecha
 order by t.Fecha asc

Doing the calculation in a single query

In this case, since what varies is a condition, you can re-write the query easily to achieve it I will always use a CTE to do the calculation of the totals and successes first and then perform the calculation of the percentage in a query on the CTE. I like this method because the formulas are easy to understand and can be easily maintained.

with
qCalculo as 
SELECT   FECHA
       , sum(
           case org_account 
             when 'OtaDMC' then CANTIDAD 
             when 'otarca1' then CANTIDAD 
           end 
         ) as OTA
       , sum(
           case 
             when result = 0 and org_account in ('OtaDMC', 'otarca1') then CANTIDAD 
           end 
         ) OTA_EXITOSO
  FROM TABLA
 WHERE FECHA >= '05/12/2018' AND FECHA < '11/12/2018'
   AND mt_msc_addr IS NOT NULL
 GROUP BY FECHA 
)
select   a.fecha
       , a.OTA
       , a.OTA_EXITOSO
       , a.OTA_EXITOSO / NullIf(a.OTA, 0) * 100 PorcentajeExitoOTA
  from qCalculo a
 order by a.Fecha asc