none
Running Totals RRS feed

  • Pregunta

  • ola quiero hacer un "running totals" pero no lo he podido hacer funcionar, he hecho un ejemplo masomenos de la consulta en donde quiero aplicar lo que he comentado (en northwind)
    SELECT     TOP (100) PERCENT dbo.[Order Details].ProductID, SUM(dbo.[Order Details].UnitPrice) AS Precio, SUM(dbo.[Order Details].Quantity) AS Cantidad 
    FROM         dbo.[Order Details] INNER JOIN 
                          dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID 
    GROUP BY dbo.[Order Details].ProductID 
    ORDER BY dbo.[Order Details].ProductID 




    como pueden ver si esta consulta la probamos nos devolvera 3 columnas: ProductoID,Precio y Cantidad.
    Ahora,lo que yo quiero hacer es agregar un columna más (running total) pero que sea el resultado de las columas precio y cantidad.

    Finalmente quiero decir que en la verdadera consulta que estoy haciendo tengo cuatro columas como cantidad y precio, osea que quiero dos running totals.

    De antemano muchas gracias..

    Nota: Disculpen mi ignorancia


    desarrollador .NET
    sábado, 7 de marzo de 2009 2:24

Respuestas

  •  Hola segun entiendo lo que quieres es agregar una columna calculada al query que te diga cual es el total de multiplicar precio  * cantidad por cada producto de la tabla order details, para eso podrias usar una consulta del siguiente estilo usando como ejemplo la bd AdventureWorks2008

    SELECT  ProductID,   
            SUM(UnitPrice) UnitPrice,   
            SUM(OrderQty) OrderQty,   
            (SUM(UnitPrice) * SUM(OrderQty)) TotalByProduct  
    FROM Sales.SalesOrderDetail  
    GROUP BY ProductID  
    ORDER BY ProductID 

    Esto daria como resultado


    Ahora acerca del running total es usado para manejar sumatorias a nivel de grupos anidados en algunos casos en otros como el que te voy a poner a continuacion lo que hace es llevar la sumatoria del valor del registro anterior mas el actual y asi sucesivamente observalo

    SELECT  ProductID,   
            SUM(UnitPrice) UnitPrice,   
            (SELECT SUM(UnitPrice)   
            FROM Sales.SalesOrderDetail   
            WHERE ProductID <= O.ProductID) RunningTotal  
    FROM Sales.SalesOrderDetail O  
    GROUP BY ProductID  
    ORDER BY ProductID 

    Con esta consulta lograrias algo como el siguiente donde en el primer registro veras el precio unitario de ese producto, en el segundo veras la sumatoria del primer producto mas el segundo, en el tercero la sumatoria  del primero mas el segundo mas el tercero y asi sucesivamente

     

    Espero que puedas encontrar aqui la respuesta y comprendas mas acerca del concepto de running totals saludos
    Andrés González
    sábado, 7 de marzo de 2009 4:15
  • Hola si te fijas el rollo lo estas teniendo por el agrupamiento que tienes a nivel del codigo de la cuenta el cual no estas teniendo en cuenta en el RunningTotal ya que alli solo tenemos en cuenta el mes (osea hace la sumatoria de todos los valores del mes sin discriminar por codigo de cuenta) ... asi deberias probar con un script como este

    SELECT  d.MesPro,     
            m.DesMes,     
            p.CodCta,      
            SUM(d.DebMna) AS DebMna,      
            SUM(d.HabMna) AS HabMna,        
            (SELECT SUM(DebMna) - SUM(HabMna)                 
            FROM  CDiario INNER JOIN    
            CPlanCuentas ON CDiario.IdCta = CPlanCuentas.IdCta                  
            WHERE MesPro <= d.MesPro  
            AND CodCta <= p.CodCta  
            AND CodCta LIKE '42%'AS Running        
    FROM    CDiario d INNER JOIN      
            CPlanCuentas p ON d.IdCta = p.IdCta INNER JOIN      
            CMesesPro m ON d.MesPro = m.MesPro                 
    WHERE   p.CodCta LIKE '42%'             
    GROUP BY d.MesPro, m.DesMes, p.CodCta      
    ORDER BY d.MesPro, p.CodCta 

    Espero que finalmente te funcione con este script saludos
    Andrés González
    • Marcado como respuesta becavas lunes, 9 de marzo de 2009 18:33
    lunes, 9 de marzo de 2009 18:07

Todas las respuestas

  •  Hola segun entiendo lo que quieres es agregar una columna calculada al query que te diga cual es el total de multiplicar precio  * cantidad por cada producto de la tabla order details, para eso podrias usar una consulta del siguiente estilo usando como ejemplo la bd AdventureWorks2008

    SELECT  ProductID,   
            SUM(UnitPrice) UnitPrice,   
            SUM(OrderQty) OrderQty,   
            (SUM(UnitPrice) * SUM(OrderQty)) TotalByProduct  
    FROM Sales.SalesOrderDetail  
    GROUP BY ProductID  
    ORDER BY ProductID 

    Esto daria como resultado


    Ahora acerca del running total es usado para manejar sumatorias a nivel de grupos anidados en algunos casos en otros como el que te voy a poner a continuacion lo que hace es llevar la sumatoria del valor del registro anterior mas el actual y asi sucesivamente observalo

    SELECT  ProductID,   
            SUM(UnitPrice) UnitPrice,   
            (SELECT SUM(UnitPrice)   
            FROM Sales.SalesOrderDetail   
            WHERE ProductID <= O.ProductID) RunningTotal  
    FROM Sales.SalesOrderDetail O  
    GROUP BY ProductID  
    ORDER BY ProductID 

    Con esta consulta lograrias algo como el siguiente donde en el primer registro veras el precio unitario de ese producto, en el segundo veras la sumatoria del primer producto mas el segundo, en el tercero la sumatoria  del primero mas el segundo mas el tercero y asi sucesivamente

     

    Espero que puedas encontrar aqui la respuesta y comprendas mas acerca del concepto de running totals saludos
    Andrés González
    sábado, 7 de marzo de 2009 4:15
  • Creo que no me deje entender bien,aquí  explico lo que en verdad lo que quiero:

      mes             cta          debeMna              haberMna   RUN_TOTAL
    ----------------------------------------------------------------------------
    1 ENERO      42121         500                     200            300
    2 FEBRERO   42121         200                    400             100    
    3 MARZO      42121         50                      100             50    
    4 ABRIL        42121         10                        50             10    


    para formar mi columna run_total  fila1 debo restar  debe(500)-haber(200)=300
    para formar mi columna runt_total fila 2 debo de sumar  run_tolta(300 de fila1) +debe(200 fila 2)-haber(400 fila2)=100

    y asi sucesivamente,

    masomenos la consulta de donde saco o quiero sacar los datos es esta:


    select m.mespro,m.DesMes,p.CodCta, Sum(DebMna) as DebMna,  
    sum(HabMna) as HabMna,'0.0'AS RUN_TOTAL  
    from CDiario d inner join CPlanCuentas  p on d.IdCta =p.IdCta  inner join CMesesPro m on d.MesPro=m.MesPro  
    where p.codcta like '42%' group by m.mespro,m.DesMes,p.CodCta order by m.mespro 


    nota: si me dices como insertas imágenes podrìa ser mas gràfica mi consulta jejeje


    desarrollador .NET
    sábado, 7 de marzo de 2009 15:02
  • Hola creo que ahora si te capte imagino que tienes una tabla de este estilo

     

    Ahora mediante esta consulta realizo la operacion para obtener el resultado que esperas

    SELECT  MONTH(Fecha) Mes,   
            Codigo,   
            SUM(Debe) Debe,   
            SUM(Haber) Haber,  
            (SELECT SUM(Debe) - SUM(Haber)  
            FROM Cuentas      
            WHERE MONTH(Fecha) <= MONTH(Cta.Fecha)) RunningTotal  
    FROM    Cuentas Cta  
    GROUP BY MONTH(Fecha), Codigo  
    ORDER BY MONTH(Fecha), Codigo 


    Este es el resultado final de la consulta antes construida

     

    Espero sea esta la respuesta que buscabas y la marques como tal saludos
    Andrés González
    sábado, 7 de marzo de 2009 15:50
  • pues yo diría que casi cojes la idea jejejeje....ya que si te fijas bien que para devolver los resultados del pequeño cuadro que te di no uso una sola tabla, es mas uso "inner join". Aplique lo que me dijistes pero me sale un error..aqui te dejo la consulta y el error..espero me puedas ayudar..gracias

    select d.mespro,m.DesMes,p.CodCta, Sum(d.DebMna) as DebMna,     
    sum(d.HabMna) as HabMna,(SELECT SUM(Debmna) - SUM(Habmna)     
            FROM  CDiario       
            WHERE (MesPro <= d.MesPro)) as running  
    from CDiario d inner join CPlanCuentas  p on d.IdCta =p.IdCta  inner join CMesesPro m on d.MesPro=m.MesPro     
    where p.codcta like '42%' 
     group by d.mespro,m.DesMes,p.CodCta order by m.mespro   

    y el error:

    Msg 8127, Level 16, State 1, Line 7

    Column "CMesesPro.MesPro" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

    sorry por mi ignorancia... y cuentame por favor como insertar las imagenes que yo no puedo...Gracias otra vez


    desarrollador .NET
    domingo, 8 de marzo de 2009 5:43
  • Si vi que usabas una segunda y hasta una tercera tabla pero mi objetivo en el post era orientarte de como podrias usar el RunningTotal que describias en un ambiente similar al tuyo mas no igual; para que lo interpretaras en tus consultas y creo arrojo los resultados esperados. El error que te esta apareciendo es muy basico no veo como se te paso simplemente tienes que tener en cuenta que los campos que uses en el select sin funciones de aggregate, en el having o en el order by tienes que ponerlos tambien a nivel del group by como te lo sugiere el mensaje de error que te arrojo el motor el cual es  muy claro. Me imagino deberia quedar algo de este estilo donde si ordenas por el campo mespro de la tabla cmespro tambien debes agregarlo al group by o usar en el order by el campo d.mespro el cual ya tenias en el group by

    select d.mespro,m.DesMes,p.CodCta, Sum(d.DebMna) as DebMna,        
    sum(d.HabMna) as HabMna,(SELECT SUM(Debmna) - SUM(Habmna)        
            FROM  CDiario          
            WHERE (MesPro <= d.MesPro)) as running     
    from CDiario d inner join CPlanCuentas  p on d.IdCta =p.IdCta  inner join CMesesPro m on d.MesPro=m.MesPro        
    where p.codcta like '42%'    
     group by d.mespro,m.DesMes,p.CodCta, m.mespro order by m.mespro  

    En cuanto a lo de insertar imagenes no entiendo muy bien tu objetivo ... si quieres insertar imagenes en un campo de tipo varbinary (max) en una tabla aqui te dejo e link donde respondi esa pregunta saludos
    Andrés González
    domingo, 8 de marzo de 2009 16:09
  • Ddisculpa mi ignorancia amigo ya que soy novato aún, lo de las imagenes me referia a las que pones en las respuestas que me das, por ejemplo ahora ya no me sale el error pero  me sale duplicado el valor del runnig Tortal en todas las filas y quisiera enviarte la img para que lo veas mejor..no se que pueda ser  ahora..disculpa tanta molestia..


    select d.mespro,m.DesMes,P.CodCta, Sum(DebMna) as DebMna, sum(HabMna) as HabMna,  
    (SELECT SUM(DebMna) - SUM(HabMna)           
            FROM  CDiario             
            WHERE (MesPro <= d.MesPro)) as Running  
    from CDiario d inner join CPlanCuentas  p on d.IdCta =p.IdCta  inner join CMesesPro m on d.MesPro=m.MesPro           
    where p.codcta like '42%'       
    group by d.mespro,m.DesMes,p.CodCta order by d.mespro 

    desarrollador .NET
    lunes, 9 de marzo de 2009 15:50
  • Hola como vas mo te preocupes que no es molestia para eso estamos ... las imagenes las subo primero a un sitio web en este caso mi blog y luego en la parte inferior del panel donde escribes la respuesta en la parte inferior izquierda me posiciono sobre el boton <> el que esta junto al lapiz (este me permite editar mi entrada como html)y alli agrego una etiqueta <img src="url_de_la_imagen_que_subi_a_mi_blog" />

    Te envio el query que me pasas de nuevo le agregue el filtro del CodCta a nivel del Running Total pues te hacia falta

    SELECT  d.MesPro,  
            m.DesMes,  
            p.CodCta,   
            SUM(d.DebMna) AS DebMna,   
            SUM(d.HabMna) AS HabMna,     
            (SELECT SUM(DebMna) - SUM(HabMna)              
            FROM  CDiario INNER JOIN 
            CPlanCuentas ON CDiario.IdCta = CPlanCuentas.IdCta               
            WHERE MesPro <= d.MesPro  
            AND CodCta LIKE '42%'AS Running     
    FROM    CDiario d INNER JOIN   
            CPlanCuentas p ON d.IdCta = p.IdCta INNER JOIN   
            CMesesPro m ON d.MesPro = m.MesPro              
    WHERE   p.CodCta LIKE '42%'          
    GROUP BY d.MesPro, m.DesMes, p.CodCta   
    ORDER BY d.MesPro 

    Correlo, revisalo, interpretalo y cuentame cuales son los valores que te arrojan porque aparentemente la formula de la columna Running esta bien a excepcion del filtro que aqui te adjunto. Talvez entre los dos podamos encontrar la causa de los valores que me cuentas te arroja el query saludos
    Andrés González
    lunes, 9 de marzo de 2009 16:20
  • pues aún no sale.
    mira cuando la consulta solo me devuelve una fila  el valor del running total  es correcto, pero cuando me devuelve mas filas es el problema:

    img

    img2 






    desarrollador .NET
    lunes, 9 de marzo de 2009 17:12
  • Hola si te fijas el rollo lo estas teniendo por el agrupamiento que tienes a nivel del codigo de la cuenta el cual no estas teniendo en cuenta en el RunningTotal ya que alli solo tenemos en cuenta el mes (osea hace la sumatoria de todos los valores del mes sin discriminar por codigo de cuenta) ... asi deberias probar con un script como este

    SELECT  d.MesPro,     
            m.DesMes,     
            p.CodCta,      
            SUM(d.DebMna) AS DebMna,      
            SUM(d.HabMna) AS HabMna,        
            (SELECT SUM(DebMna) - SUM(HabMna)                 
            FROM  CDiario INNER JOIN    
            CPlanCuentas ON CDiario.IdCta = CPlanCuentas.IdCta                  
            WHERE MesPro <= d.MesPro  
            AND CodCta <= p.CodCta  
            AND CodCta LIKE '42%'AS Running        
    FROM    CDiario d INNER JOIN      
            CPlanCuentas p ON d.IdCta = p.IdCta INNER JOIN      
            CMesesPro m ON d.MesPro = m.MesPro                 
    WHERE   p.CodCta LIKE '42%'             
    GROUP BY d.MesPro, m.DesMes, p.CodCta      
    ORDER BY d.MesPro, p.CodCta 

    Espero que finalmente te funcione con este script saludos
    Andrés González
    • Marcado como respuesta becavas lunes, 9 de marzo de 2009 18:33
    lunes, 9 de marzo de 2009 18:07
  • yeeeeee, exelente Andrés!!!, ahora si salio, vaya que si tengo mucho que aprender para alcanzarte jejeje, pero estoy contento por tu paciencia y por lo que aprendi....te cometo que lei varios libros y todos hacen cosas basicas, nada como esto, espero que si conoces un libro bueno me lo digas para profundizar mas en todo esto.

    Una ultima duda, ya que esta consulta lo aplicare en una buena cantidad de registros....,¿Podría hacerse lento?...si es asi..como podría optimizarla..

    Mil Gracias !!

    desarrollador .NET
    lunes, 9 de marzo de 2009 18:33