none
Referencias cruzadas

    Question

  • Hola estaba viedo este link http://www.elguille.info/NET/ADONET/firmas_salva_Pivot_Unpivot.htm
    , me pareció muy interesante, justo tengo que hacer un reporte muy similar, pero tendria que agregarle un total al final de cada fila que totalize por mes y cliente.Es decir que haga la sumatoria horizontal.

    Como se podria hacer ello.

    Saludos.

    Saturday, January 15, 2011 6:45 PM

Answers

  • Cual metodo estas usando, metodo tradicional que usa la funcion de agregacion con expresion CASE SUM(CASE...), o el operador PIVOT?

    En cualquiera de los dos casos, tendras que agregar una columna que cuente todas las filas que machan la condicion de agrupacion como count(*) para el primer metodo, o la suma de todos los meses para el segundo.

    Veamos un ejemplo usando ambos metodos.

    USE Northwind;
    GO
    -- cantidad de ordenes por cliente y mes
    -- y total del año
    SELECT
    	CustomerID,
    	SUM(CASE WHEN MONTH(OrderDate) = 1 THEN 1 ELSE 0 END) AS Ene,
    	SUM(CASE WHEN MONTH(OrderDate) = 2 THEN 1 ELSE 0 END) AS Feb,
    	SUM(CASE WHEN MONTH(OrderDate) = 3 THEN 1 ELSE 0 END) AS Mar,
    	SUM(CASE WHEN MONTH(OrderDate) = 4 THEN 1 ELSE 0 END) AS Abr,
    	SUM(CASE WHEN MONTH(OrderDate) = 5 THEN 1 ELSE 0 END) AS May,
    	SUM(CASE WHEN MONTH(OrderDate) = 6 THEN 1 ELSE 0 END) AS Jun,
    	SUM(CASE WHEN MONTH(OrderDate) = 7 THEN 1 ELSE 0 END) AS Jul,
    	SUM(CASE WHEN MONTH(OrderDate) = 8 THEN 1 ELSE 0 END) AS Ago,
    	SUM(CASE WHEN MONTH(OrderDate) = 9 THEN 1 ELSE 0 END) AS Sep,
    	SUM(CASE WHEN MONTH(OrderDate) = 10 THEN 1 ELSE 0 END) AS Oct,
    	SUM(CASE WHEN MONTH(OrderDate) = 11 THEN 1 ELSE 0 END) AS Nov,
    	SUM(CASE WHEN MONTH(OrderDate) = 12 THEN 1 ELSE 0 END) AS Dic,
    	COUNT(*) AS Total
    FROM
    	dbo.Orders
    WHERE
      OrderDate >= '19980101' AND OrderDate < '19990101'
    GROUP BY
      CustomerID
    ORDER BY 
      CustomerID;
    GO
    SET LANGUAGE spanish;
    GO
    SELECT
    	CustomerID,
    	ISNULL(Ene, 0) AS Ene,
    	ISNULL(Feb, 0) AS Feb,
    	ISNULL(Mar, 0) AS Mar,
    	ISNULL(Abr, 0) AS Abr,
    	ISNULL(May, 0) AS May,
    	ISNULL(Jun, 0) AS Jun,
    	ISNULL(Jul, 0) AS Jul,
    	ISNULL(Ago, 0) AS Ago,
    	ISNULL(Sep, 0) AS Sep,
    	ISNULL(Oct, 0) AS Oct,
    	ISNULL(Nov, 0) AS Nov,
    	ISNULL(Dic, 0) AS Dic,
    	ISNULL(Ene, 0) + ISNULL(Feb, 0) + ISNULL(Mar, 0) +
    	ISNULL(Abr, 0) + ISNULL(May, 0) + ISNULL(Jun, 0) +
    	ISNULL(Jul, 0) + ISNULL(Ago, 0) + ISNULL(Sep, 0) +
    	ISNULL(Oct, 0) + ISNULL(Nov, 0) + ISNULL(Dic, 0) AS Total
    FROM
      (
      SELECT CustomerID, LEFT(DATENAME([month], OrderDate), 3) AS month_name, 1 AS dummy 
      FROM dbo.Orders
      WHERE OrderDate >= '19980101' AND OrderDate < '19990101'
      ) AS C
      PIVOT
      (
      SUM(dummy)
      FOR month_name IN ([Ene], [Feb], [Mar], [Abr], [May], [Jun], [Jul], [Ago], [Sep], [Oct], [Nov], [Dic])
      ) AS P
    ORDER BY 
      CustomerID;
    GO
    SET LANGUAGE english;
    GO
    

     


    AMB

    Some guidelines for posting questions...

    Sunday, January 16, 2011 1:35 AM
  • Depende de lo que estes sumando, pero si te fijas bien lo que estamos haciendo es filtrando todas las ordenes por cliente para el año 1998. Asi que todo cliente que aperece en el resultado tiene al menos una fila que macha la condicion de filtro. Si estamos agrupando por cliente entonces COUNT(*) es el # total de ordenes por cliente en ese año, asi que ya tenemos el total pero tambien vamos a partir ese total por cada mes del año.

    Veamos un ejemplo n concreto. El cliente ALFKI  tiene tres ordenes en ese año ( count(*) ), una en Enero, otra en Marzo, y otra en Abril. usando la funcion de agrupacion SUM y la expresion CASE repartimos las ordenes por mes, ya que estamos agrupando por cliente tambien tenemos el total de filas al usar count(*), tambien pudieras usar SUM(1).

     


    AMB

    Some guidelines for posting questions...

    • Marked as answer by Gepetto456 Tuesday, January 18, 2011 7:09 PM
    Sunday, January 16, 2011 6:06 PM

All replies

  • Cual metodo estas usando, metodo tradicional que usa la funcion de agregacion con expresion CASE SUM(CASE...), o el operador PIVOT?

    En cualquiera de los dos casos, tendras que agregar una columna que cuente todas las filas que machan la condicion de agrupacion como count(*) para el primer metodo, o la suma de todos los meses para el segundo.

    Veamos un ejemplo usando ambos metodos.

    USE Northwind;
    GO
    -- cantidad de ordenes por cliente y mes
    -- y total del año
    SELECT
    	CustomerID,
    	SUM(CASE WHEN MONTH(OrderDate) = 1 THEN 1 ELSE 0 END) AS Ene,
    	SUM(CASE WHEN MONTH(OrderDate) = 2 THEN 1 ELSE 0 END) AS Feb,
    	SUM(CASE WHEN MONTH(OrderDate) = 3 THEN 1 ELSE 0 END) AS Mar,
    	SUM(CASE WHEN MONTH(OrderDate) = 4 THEN 1 ELSE 0 END) AS Abr,
    	SUM(CASE WHEN MONTH(OrderDate) = 5 THEN 1 ELSE 0 END) AS May,
    	SUM(CASE WHEN MONTH(OrderDate) = 6 THEN 1 ELSE 0 END) AS Jun,
    	SUM(CASE WHEN MONTH(OrderDate) = 7 THEN 1 ELSE 0 END) AS Jul,
    	SUM(CASE WHEN MONTH(OrderDate) = 8 THEN 1 ELSE 0 END) AS Ago,
    	SUM(CASE WHEN MONTH(OrderDate) = 9 THEN 1 ELSE 0 END) AS Sep,
    	SUM(CASE WHEN MONTH(OrderDate) = 10 THEN 1 ELSE 0 END) AS Oct,
    	SUM(CASE WHEN MONTH(OrderDate) = 11 THEN 1 ELSE 0 END) AS Nov,
    	SUM(CASE WHEN MONTH(OrderDate) = 12 THEN 1 ELSE 0 END) AS Dic,
    	COUNT(*) AS Total
    FROM
    	dbo.Orders
    WHERE
      OrderDate >= '19980101' AND OrderDate < '19990101'
    GROUP BY
      CustomerID
    ORDER BY 
      CustomerID;
    GO
    SET LANGUAGE spanish;
    GO
    SELECT
    	CustomerID,
    	ISNULL(Ene, 0) AS Ene,
    	ISNULL(Feb, 0) AS Feb,
    	ISNULL(Mar, 0) AS Mar,
    	ISNULL(Abr, 0) AS Abr,
    	ISNULL(May, 0) AS May,
    	ISNULL(Jun, 0) AS Jun,
    	ISNULL(Jul, 0) AS Jul,
    	ISNULL(Ago, 0) AS Ago,
    	ISNULL(Sep, 0) AS Sep,
    	ISNULL(Oct, 0) AS Oct,
    	ISNULL(Nov, 0) AS Nov,
    	ISNULL(Dic, 0) AS Dic,
    	ISNULL(Ene, 0) + ISNULL(Feb, 0) + ISNULL(Mar, 0) +
    	ISNULL(Abr, 0) + ISNULL(May, 0) + ISNULL(Jun, 0) +
    	ISNULL(Jul, 0) + ISNULL(Ago, 0) + ISNULL(Sep, 0) +
    	ISNULL(Oct, 0) + ISNULL(Nov, 0) + ISNULL(Dic, 0) AS Total
    FROM
      (
      SELECT CustomerID, LEFT(DATENAME([month], OrderDate), 3) AS month_name, 1 AS dummy 
      FROM dbo.Orders
      WHERE OrderDate >= '19980101' AND OrderDate < '19990101'
      ) AS C
      PIVOT
      (
      SUM(dummy)
      FOR month_name IN ([Ene], [Feb], [Mar], [Abr], [May], [Jun], [Jul], [Ago], [Sep], [Oct], [Nov], [Dic])
      ) AS P
    ORDER BY 
      CustomerID;
    GO
    SET LANGUAGE english;
    GO
    

     


    AMB

    Some guidelines for posting questions...

    Sunday, January 16, 2011 1:35 AM
  • Hola Hunchback estaba usando la primera forma , tengo una duda , como es que el count(*) cuenta los valores de que asigna el case , yo pense que contaba filas en cuyo caso debería salir 1 en todas las filas.

    Saludos

    Sunday, January 16, 2011 3:15 PM
  • Depende de lo que estes sumando, pero si te fijas bien lo que estamos haciendo es filtrando todas las ordenes por cliente para el año 1998. Asi que todo cliente que aperece en el resultado tiene al menos una fila que macha la condicion de filtro. Si estamos agrupando por cliente entonces COUNT(*) es el # total de ordenes por cliente en ese año, asi que ya tenemos el total pero tambien vamos a partir ese total por cada mes del año.

    Veamos un ejemplo n concreto. El cliente ALFKI  tiene tres ordenes en ese año ( count(*) ), una en Enero, otra en Marzo, y otra en Abril. usando la funcion de agrupacion SUM y la expresion CASE repartimos las ordenes por mes, ya que estamos agrupando por cliente tambien tenemos el total de filas al usar count(*), tambien pudieras usar SUM(1).

     


    AMB

    Some guidelines for posting questions...

    • Marked as answer by Gepetto456 Tuesday, January 18, 2011 7:09 PM
    Sunday, January 16, 2011 6:06 PM
  • Hola hunchback, gracias por tus respuestas, me he fijado que en la query que te pase de esa pagina  se agrupa por producto y en tu ejemplo tu lo agrupas por customerid, pero si yo necesito el total de productos por cada año.Ahi como lo haría.

    Saludos.

    Sería agregarle solo esto

    sum

     

    (d.quantity) AS TOTAL

    Tuesday, January 18, 2011 9:00 PM