Home » SQL & PL/SQL » SQL & PL/SQL » Totalizing a column (merged)
Totalizing a column (merged) [message #404406] Thu, 21 May 2009 12:58 Go to next message
leon945
Messages: 3
Registered: May 2009
Location: Monterrey, Mexico
Junior Member
Hi,
I have this query..

select telefonoorigen as numerotelefonico,rc.idsitio,t.descripcion,sf.descripcion as nomSitio,rh.idtipollamada,count(*) llamadas,SUM(duracionmins) minutos,
(select case when sum(rcc.cantidad)>0 then 
sum(rcc.cantidad) else 0 end 
from rcantidadcontratado rcc 
where rcc.idtipollamada=rh.idtipollamada and 
rcc.IDSITIO= rc.idsitio  and 
(rcc.FECHACIERRE IS NULL OR rcc.FECHACIERRE >=200904) AND 
(rcc.FECHAAPLICA <=200904) AND (rcc.ONNET = 0) ) as incluidas,case when 
((select case when 
sum(cantidad)>0 then 
sum(cantidad) else 0 end 
from rcantidadcontratado rcc where rcc.idtipollamada=rh.idtipollamada and 
rcc.IDSITIO=rc.idsitio and 
(rcc.FECHACIERRE IS NULL OR rcc.FECHACIERRE >=200904) AND 
(rcc.FECHAAPLICA <=200904)) - count(*))<0 then 
((select case when sum(cantidad)>0 then 
sum(cantidad) else 0 end 
from rcantidadcontratado rcc where rcc.idtipollamada=rh.idtipollamada and 
rcc.IDSITIO=rc.idsitio and 
(rcc.FECHACIERRE IS NULL OR rcc.FECHACIERRE >=200904) AND 
(rcc.FECHAAPLICA <=200904)  ) - count(*)) *-1 else 0 end as noincluidas,
case when ((select case when 
sum(cantidad)>0 then 
sum(cantidad) else 0 end 
from rcantidadcontratado rcc where rcc.idtipollamada=rh.idtipollamada and 
rcc.IDSITIO=rc.idsitio and 
(rcc.FECHACIERRE IS NULL OR Rcc.FECHACIERRE >=200904) AND 
(rcc.FECHAAPLICA <=200904)) - count(*))<0 then 
((select case when sum(cantidad)>0 then sum(cantidad) else 0 end 
from rcantidadcontratado rcc where rcc.idtipollamada=rh.idtipollamada and 
rcc.IDSITIO=rc.idsitio and 
(rcc.FECHACIERRE IS NULL OR Rcc.FECHACIERRE >=200904) AND 
(rcc.FECHAAPLICA <=200904)) - count(*))*-1 else 0 end * (select importeactual from tipollamada where idtipollamada=rh.idtipollamada) as total,
(select importeactual from tipollamada where idtipollamada=rh.idtipollamada) as tarifa
from rhistorialdetallellamadas rh 
INNER JOIN tipollamada t ON(rh.idtipollamada=t.idtipollamada) 
INNER JOIN rclientenumerotelefonico rc ON(TO_CHAR(rc.numerotelefonico)=rh.telefonoorigen) 
INNER JOIN cliente c ON(c.idcliente=rc.idcliente and c.idtipocliente=2) 
INNER JOIN sitiofactura sf on(sf.idsitiofactura=rc.idsitio) 
where c.idcliente=8630 and 
TO_NUMBER(TO_CHAR(TO_DATE(fechallamada,'DD/MM/YYYY'),'MM'))=04 and 
TO_NUMBER(TO_CHAR(TO_DATE(fechallamada,'DD/MM/YYYY'),'YYYY'))=2009 AND 
TO_DATE(rh.FECHALLAMADA,'DD/MM/YYYY')>=TO_DATE(C.FECHAACTIVACION,'DD/MM/YYYY') 
group by telefonoorigen,rc.idsitio,sf.descripcion,rh.idtipollamada,t.descripcion 
order by telefonoorigen,rc.idsitio,idtipollamada,llamadas desc;


Sorry if it's a mess..
Here a screenshot of the results, and what i dont know how to do..
I want the "TOTAL" column to be totalized every time the "NUMEROTELEFONICO" changes..

Could anyone help me with this please?
I'd appreciate it.. thanks in advanced
  • Attachment: datos.jpg
    (Size: 122.12KB, Downloaded 53 times)
Re: Totalizing a column [message #404408 is a reply to message #404406] Thu, 21 May 2009 13:08 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
SELECT   telefonoorigen                                  AS numerotelefonico,
         rc.idsitio,
         t.descripcion,
         sf.descripcion                                  AS nomsitio,
         rh.idtipollamada,
         Count(* )                                       llamadas,
         Sum(duracionmins)                               minutos,
         (SELECT CASE
                   WHEN Sum(rcc.cantidad) > 0 THEN Sum(rcc.cantidad)
                   ELSE 0
                 END
          FROM   rcantidadcontratado rcc
          WHERE  rcc.idtipollamada = rh.idtipollamada
                 AND rcc.idsitio = rc.idsitio
                 AND (rcc.fechacierre IS NULL
                       OR rcc.fechacierre >= 200904)
                 AND (rcc.fechaaplica <= 200904)
                 AND (rcc.onnet = 0)) AS incluidas,
         CASE
           WHEN ((SELECT CASE
                           WHEN Sum(cantidad) > 0 THEN Sum(cantidad)
                           ELSE 0
                         END
                  FROM   rcantidadcontratado rcc
                  WHERE  rcc.idtipollamada = rh.idtipollamada
                         AND rcc.idsitio = rc.idsitio
                         AND (rcc.fechacierre IS NULL
                               OR rcc.fechacierre >= 200904)
                         AND (rcc.fechaaplica <= 200904)) - Count(* )) < 0 THEN ((SELECT CASE
                                                                                           WHEN Sum(cantidad) > 0 THEN Sum(cantidad)
                                                                                           ELSE 0
                                                                                         END
                                                                                  FROM   rcantidadcontratado rcc
                                                                                  WHERE  rcc.idtipollamada = rh.idtipollamada
                                                                                         AND rcc.idsitio = rc.idsitio
                                                                                         AND (rcc.fechacierre IS NULL
                                                                                               OR rcc.fechacierre >= 200904)
                                                                                         AND (rcc.fechaaplica <= 200904)) - Count(* )) *- 1
           ELSE 0
         END AS noincluidas,
         CASE
           WHEN ((SELECT CASE
                           WHEN Sum(cantidad) > 0 THEN Sum(cantidad)
                           ELSE 0
                         END
                  FROM   rcantidadcontratado rcc
                  WHERE  rcc.idtipollamada = rh.idtipollamada
                         AND rcc.idsitio = rc.idsitio
                         AND (rcc.fechacierre IS NULL
                               OR rcc.fechacierre >= 200904)
                         AND (rcc.fechaaplica <= 200904)) - Count(* )) < 0 THEN ((SELECT CASE
                                                                                           WHEN Sum(cantidad) > 0 THEN Sum(cantidad)
                                                                                           ELSE 0
                                                                                         END
                                                                                  FROM   rcantidadcontratado rcc
                                                                                  WHERE  rcc.idtipollamada = rh.idtipollamada
                                                                                         AND rcc.idsitio = rc.idsitio
                                                                                         AND (rcc.fechacierre IS NULL
                                                                                               OR rcc.fechacierre >= 200904)
                                                                                         AND (rcc.fechaaplica <= 200904)) - Count(* )) *- 1
           ELSE 0
         END * (SELECT importeactual
                FROM   tipollamada
                WHERE  idtipollamada = rh.idtipollamada) AS total,
         (SELECT importeactual
          FROM   tipollamada
          WHERE  idtipollamada = rh.idtipollamada) AS tarifa
FROM     rhistorialdetallellamadas rh
         INNER JOIN tipollamada t
           ON (rh.idtipollamada = t.idtipollamada)
         INNER JOIN rclientenumerotelefonico rc
           ON (To_char(rc.numerotelefonico) = rh.telefonoorigen)
         INNER JOIN cliente c
           ON (c.idcliente = rc.idcliente
               AND c.idtipocliente = 2)
         INNER JOIN sitiofactura sf
           ON (sf.idsitiofactura = rc.idsitio)
WHERE    c.idcliente = 8630
         AND To_number(To_char(To_date(fechallamada,'DD/MM/YYYY'),'MM')) = 04
         AND To_number(To_char(To_date(fechallamada,'DD/MM/YYYY'),'YYYY')) = 2009
         AND To_date(rh.fechallamada,'DD/MM/YYYY') >= To_date(c.fechaactivacion,'DD/MM/YYYY')
GROUP BY telefonoorigen,
         rc.idsitio,
         sf.descripcion,
         rh.idtipollamada,
         t.descripcion
ORDER BY telefonoorigen,
         rc.idsitio,
         idtipollamada,
         llamadas DESC; 
Re: Totalizing a column [message #404410 is a reply to message #404408] Thu, 21 May 2009 13:14 Go to previous messageGo to next message
leon945
Messages: 3
Registered: May 2009
Location: Monterrey, Mexico
Junior Member
Thanks for ordering/organizing the code..
Smile

[Updated on: Thu, 21 May 2009 13:17]

Report message to a moderator

Re: Totalizing a column [message #404416 is a reply to message #404410] Thu, 21 May 2009 13:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you want to do it yourself next time, the Posting Guidelines (link in top-part of the forum page) contains hints & tips on how to format your code.
Re: Totalizing a column [message #404424 is a reply to message #404416] Thu, 21 May 2009 15:08 Go to previous message
leon945
Messages: 3
Registered: May 2009
Location: Monterrey, Mexico
Junior Member
thank you, i'll take that into account next time..
sorry for the trouble.. Confused
Previous Topic: Should I use global temporary table?
Next Topic: Outer join creates multiple
Goto Forum:
  


Current Time: Sat Dec 10 08:59:56 CST 2016

Total time taken to generate the page: 0.11506 seconds