none
Contar valores de campos para realizar promedio de a cuerdo a los datos

    Question

  • Buenas tardes compañeros del foro.

    Formulo la siguiente pregunta con el fin de que alguien pueda ayudarme puesto que mi conocimiento en sql server es limitado.

    Estoy realizando unas operaciones con sql server 2008, pero diseñé un formulario en Visual Studio 2008…

    Tengo campos específicos para cada registro…

    Ejemplo:

    Empresa      # folio      comedor   alarma   estacionamiento   punto/reunión   puerta/emergencia    total/Puntos  promedio             __________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________

    GSSAT     12457852       1             1              N/A                         1                             1                         4                   100%  

      __________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________

    EMERSON  54125458     1              0                1                         1                             0                         3                     40% 

      __________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________

    Nokia     78454841        0             N/A             0                          1                             1                         2                     50% 

      __________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________

    LOGOS  45142123         1              N/A           N/A                       1                             1                         3                    100% 

    Como verán es una tabla con 5 campos a evaluar(comedor, alarma, estacionamiento, punto/reunión y puerta/emergencia), con 1, 0 ó con N/A.

    lo que quiero es que me dé exactamente lo que muestra la tabla en el campo total/puntos y promedio, que cuando en algunos o los dos campos(estacionamiento y alarma),

    estén en N/A, cuando sea el momento de realizar la suma para sacar el promedio no los tome en cuenta y sólo suma los campos con 1 y 0, y esos campos conformen el

    100% total…  Tal y como se observa en el último registro de la empresa LOGOS DE MEXICO.

    Me pueden dar ideas?... Ya lo intenté con el CASE, pero no sale…

    Gracias por adelantado


    • Edited by Santiago Hdez Tuesday, December 11, 2012 10:56 PM Ortografia Incorrecta
    Tuesday, December 11, 2012 10:55 PM

Answers

  • Si estas usando SS 2005 o mayor, entonces usa el operador APPLY, la funcion NULLIF, y las funciones de agregado. Recuerda que las funciones de agregado excluyeb la marca NULL en sus calculos.

    select A.*, B.*
    from
        T as A
        outer apply
        (
        select sum(c1) as total_puntos, avg(c1) as promedio
        from
            (
            values
                (CAST(NULLIF(A.comedor, 'N/A') AS int)),
                (CAST(NULLIF(A.alarma, 'N/A') AS int)),
                (CAST(NULLIF(A.estacionamiento, 'N/A') AS int)),
                (CAST(NULLIF(A.punto_reunion, 'N/A') AS int)),
                (CAST(NULLIF(A.punto_emergencia, 'N/A') AS int)),
            ) as R(c1)
        ) as B;

    La idea es usar el operador APPLY para hacer el UNPIVOT de las columnas y pasarlas a filas para poder calcular la suma y el promedio de esas columnas. El truco esta en devolver la marca NULL si el valor de la columna es igual a 'N/A' y por lo tanto las funciones SUM y AVG excluiran esa columna.


    AMB

    Some guidelines for posting questions...

    • Marked as answer by Santiago Hdez Friday, December 28, 2012 6:47 PM
    Friday, December 28, 2012 6:03 PM
  • Las columnas alarma y estacionamiento son del tipo varchar. Es decir, usan texto ?

    De ser así cambialas al tipo int o smallint para que soporten valores numéricos y en vez de N/A que tenga el valor NULL (Nulo).


    MVP MCT MCTS Daniel Calbimonte

    http://elpaladintecnologico.blogspot.com

    Tuesday, December 11, 2012 11:41 PM

All replies

  • Las columnas alarma y estacionamiento son del tipo varchar. Es decir, usan texto ?

    De ser así cambialas al tipo int o smallint para que soporten valores numéricos y en vez de N/A que tenga el valor NULL (Nulo).


    MVP MCT MCTS Daniel Calbimonte

    http://elpaladintecnologico.blogspot.com

    Tuesday, December 11, 2012 11:41 PM
  • Gracias dani671, eso no lo puedo realizar porque los datos que se introducen a mi base de datos vienen de un Aplicacion Movil de un proveedor externo y todos los datos introducidos desde el telefono son del tipo NVARCHAR... Así que tengo que realizar la conversion en el momento de realizar el reporte en Visual Studio. Pero es mucho más complicado...
    Friday, December 28, 2012 5:42 PM
  • Si estas usando SS 2005 o mayor, entonces usa el operador APPLY, la funcion NULLIF, y las funciones de agregado. Recuerda que las funciones de agregado excluyeb la marca NULL en sus calculos.

    select A.*, B.*
    from
        T as A
        outer apply
        (
        select sum(c1) as total_puntos, avg(c1) as promedio
        from
            (
            values
                (CAST(NULLIF(A.comedor, 'N/A') AS int)),
                (CAST(NULLIF(A.alarma, 'N/A') AS int)),
                (CAST(NULLIF(A.estacionamiento, 'N/A') AS int)),
                (CAST(NULLIF(A.punto_reunion, 'N/A') AS int)),
                (CAST(NULLIF(A.punto_emergencia, 'N/A') AS int)),
            ) as R(c1)
        ) as B;

    La idea es usar el operador APPLY para hacer el UNPIVOT de las columnas y pasarlas a filas para poder calcular la suma y el promedio de esas columnas. El truco esta en devolver la marca NULL si el valor de la columna es igual a 'N/A' y por lo tanto las funciones SUM y AVG excluiran esa columna.


    AMB

    Some guidelines for posting questions...

    • Marked as answer by Santiago Hdez Friday, December 28, 2012 6:47 PM
    Friday, December 28, 2012 6:03 PM
  • Excelente HunchBack, me parece mas razonable eso, deja ver si funciona correctamente.. gracias...
    Friday, December 28, 2012 6:46 PM