none
media por ano RRS feed

  • Pergunta

  • Prezados tenho a Sql abaixo que me retorna as informaçoes que preciso, porem eu preciso que me retorne a media e depois  o total dos meses.

    somaria a todos os meses e depois retornasse a media, tem como fazer via SQl.

    SQl


    SELECT   YEAR = YEAR(DATAEMISSAO),
            CODCCUSTO,
             COALESCE(CONVERT(VARCHAR,SUM(CASE  WHEN MONTH(DATAEMISSAO) = 1 THEN VALORLIQUIDO   END), 1), '') AS 'JAN',
             COALESCE(CONVERT(VARCHAR,SUM(CASE  WHEN MONTH(DATAEMISSAO) = 2 THEN VALORLIQUIDO   END), 1), '') AS 'FEV',
             COALESCE(CONVERT(VARCHAR,SUM(CASE  WHEN MONTH(DATAEMISSAO) = 3 THEN VALORLIQUIDO   END), 1), '') AS 'MAR',
             COALESCE(CONVERT(VARCHAR,SUM(CASE  WHEN MONTH(DATAEMISSAO) = 4 THEN VALORLIQUIDO   END), 1), '') AS 'ABR',
             COALESCE(CONVERT(VARCHAR,SUM(CASE  WHEN MONTH(DATAEMISSAO) = 5 THEN VALORLIQUIDO   END), 1), '*') AS 'MAIO',
             COALESCE(CONVERT(VARCHAR,SUM(CASE  WHEN MONTH(DATAEMISSAO) = 6 THEN VALORLIQUIDO   END), 1), '') AS 'JUN',
             COALESCE(CONVERT(VARCHAR,SUM(CASE  WHEN MONTH(DATAEMISSAO) = 7 THEN VALORLIQUIDO   END), 1), '0') AS 'JUL',
             COALESCE(CONVERT(VARCHAR,SUM(CASE  WHEN MONTH(DATAEMISSAO) = 8 THEN VALORLIQUIDO   END), 1), '') AS 'AUG',
             COALESCE(CONVERT(VARCHAR,SUM(CASE  WHEN MONTH(DATAEMISSAO) = 9 THEN VALORLIQUIDO   END), 1), '') AS 'SET',
             COALESCE(CONVERT(VARCHAR,SUM(CASE  WHEN MONTH(DATAEMISSAO) = 10 THEN VALORLIQUIDO   END), 1), '') AS 'OUT',
             COALESCE(CONVERT(VARCHAR,SUM(CASE  WHEN MONTH(DATAEMISSAO) = 11 THEN VALORLIQUIDO   END), 1), '') AS 'NOV',
             COALESCE(CONVERT(VARCHAR,SUM(CASE  WHEN MONTH(DATAEMISSAO) = 12 THEN VALORLIQUIDO   END), 1), '') AS 'DEZ'
    FROM     TMOV
    WHERE YEAR(DATAEMISSAO) = '2011'   AND TMOV.CODTMV = '1.2.01'
    GROUP BY YEAR(DATAEMISSAO) ,TMOV.CODCCUSTO
    ORDER BY YEAR(DATAEMISSAO)
    GO

    obrigado a todos pela ajuda

    sexta-feira, 5 de agosto de 2011 01:36

Respostas

  • Novo Tento

    ;with cte_dados
    as(
    
    SELECT  YEAR = YEAR(DATAEMISSAO),
        CODCCUSTO,
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 1 THEN SUM(VALORLIQUIDO) END, 0) AS 'JAN',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 2 THEN SUM(VALORLIQUIDO) END, 0) AS 'FEV',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 3 THEN SUM(VALORLIQUIDO) END, 0) AS 'MAR',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 4 THEN SUM(VALORLIQUIDO) END, 0) AS 'ABR',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 5 THEN SUM(VALORLIQUIDO) END, 0) AS 'MAIO',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 6 THEN SUM(VALORLIQUIDO) END, 0) AS 'JUN',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 7 THEN SUM(VALORLIQUIDO) END, 0) AS 'JUL',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 8 THEN SUM(VALORLIQUIDO) END, 0) AS 'AUG',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 9 THEN SUM(VALORLIQUIDO) END, 0) AS 'SET',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 10 THEN SUM(VALORLIQUIDO) END, 0) AS 'OUT',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 11 THEN SUM(VALORLIQUIDO) END, 0) AS 'NOV',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 12 THEN SUM(VALORLIQUIDO) END, 0) AS 'DEZ'
    FROM   TMOV
    WHERE YEAR(DATAEMISSAO) = '2011'  AND CODTMV = '1.2.01'
    GROUP BY DATAEMISSAO,CODCCUSTO
    ), cte_soma as (select [YEAR], CODCCUSTO, SUM(JAN) as JAN, SUM(FEV) as FEV, SUM(MAR) as MAR, SUM(ABR) as ABR, SUM(MAIO) as MAIO
    , SUM(JUN) as JUN, SUM(JUL) as JUL, SUM(AUG) as AUG, SUM([SET]) as 'SET', SUM([OUT]) as 'OUT', SUM(NOV) as NOV, SUM(DEZ) as DEZ from cte_dados Group by [YEAR],CODCCUSTO)
    
    select *,JAN+FEV+MAR+ABR+MAIO+JUN+JUL+AUG+[SET]+[OUT]+NOV+DEZ as soma, (JAN+FEV+MAR+ABR+MAIO+JUN+JUL+AUG+[SET]+[OUT]+NOV+DEZ)/12 as media from cte_soma
    ORDER BY [YEAR]
    


    Att
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS.
    Se útil, classifique!!!
    Me siga no twitter: @marcelodba

    • Marcado como Resposta Ronnie Von sexta-feira, 5 de agosto de 2011 20:19
    sexta-feira, 5 de agosto de 2011 19:45

Todas as Respostas

  • Ronnie

    teste com esta CTE para SQL 2005 ou 2008... vc pode uasr o COMPUTE tbm...

    ;with cte_dados
    as(
    
    SELECT  YEAR = YEAR(DATAEMISSAO),
        CODCCUSTO,
         COALESCE(CONVERT(VARCHAR,SUM(CASE WHEN MONTH(DATAEMISSAO) = 1 THEN VALORLIQUIDO  END), 1), '') AS 'JAN',
         COALESCE(CONVERT(VARCHAR,SUM(CASE WHEN MONTH(DATAEMISSAO) = 2 THEN VALORLIQUIDO  END), 1), '') AS 'FEV',
         COALESCE(CONVERT(VARCHAR,SUM(CASE WHEN MONTH(DATAEMISSAO) = 3 THEN VALORLIQUIDO  END), 1), '') AS 'MAR',
         COALESCE(CONVERT(VARCHAR,SUM(CASE WHEN MONTH(DATAEMISSAO) = 4 THEN VALORLIQUIDO  END), 1), '') AS 'ABR',
         COALESCE(CONVERT(VARCHAR,SUM(CASE WHEN MONTH(DATAEMISSAO) = 5 THEN VALORLIQUIDO  END), 1), '*') AS 'MAIO',
         COALESCE(CONVERT(VARCHAR,SUM(CASE WHEN MONTH(DATAEMISSAO) = 6 THEN VALORLIQUIDO  END), 1), '') AS 'JUN',
         COALESCE(CONVERT(VARCHAR,SUM(CASE WHEN MONTH(DATAEMISSAO) = 7 THEN VALORLIQUIDO  END), 1), '0') AS 'JUL',
         COALESCE(CONVERT(VARCHAR,SUM(CASE WHEN MONTH(DATAEMISSAO) = 8 THEN VALORLIQUIDO  END), 1), '') AS 'AUG',
         COALESCE(CONVERT(VARCHAR,SUM(CASE WHEN MONTH(DATAEMISSAO) = 9 THEN VALORLIQUIDO  END), 1), '') AS 'SET',
         COALESCE(CONVERT(VARCHAR,SUM(CASE WHEN MONTH(DATAEMISSAO) = 10 THEN VALORLIQUIDO  END), 1), '') AS 'OUT',
         COALESCE(CONVERT(VARCHAR,SUM(CASE WHEN MONTH(DATAEMISSAO) = 11 THEN VALORLIQUIDO  END), 1), '') AS 'NOV',
         COALESCE(CONVERT(VARCHAR,SUM(CASE WHEN MONTH(DATAEMISSAO) = 12 THEN VALORLIQUIDO  END), 1), '') AS 'DEZ'
    FROM   TMOV
    WHERE YEAR(DATAEMISSAO) = '2011'  AND TMOV.CODTMV = '1.2.01'
    GROUP BY YEAR(DATAEMISSAO) ,TMOV.CODCCUSTO
    )
    select *,JAN+FEV+MAR+ABR+MAIO+JUN+JUL+AUG+[SET]+[OUT]+NOV+DEZ as soma, (JAN+FEV+MAR+ABR+MAIO+JUN+JUL+AUG+[SET]+[OUT]+NOV+DEZ)/12 as media from cte_dados
    ORDER BY [YEAR]
    GO
    


    Att.
    Marcelo Fernandes

     


    MCP, MCDBA, MCSA, MCTS.
    Se útil, classifique!!!
    Me siga no twitter: @marcelodba

    sexta-feira, 5 de agosto de 2011 13:09
  • Ola Marcelo nao sei por quer mas rodando o exemplo conforme vc me passou deu essa mensagem de erro.

     

    Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the varchar value '5752.03005073.57002595.07003924.89003246.15001353.14000' to data type int.
    Warning: Null value is eliminated by an aggregate or other SET operation.

    tentei ver alguma coisa e nao consegui,

    obrigado pela ajuda

    sexta-feira, 5 de agosto de 2011 13:28
  • Ronnie vc esta convertendo o campo VALORLIQUIDO para varchar ai nao vai conseguir somar mesmo... tsta assim

     

    ;with cte_dados
    as(
    
    SELECT  YEAR = YEAR(DATAEMISSAO),
        CODCCUSTO,
         COALESCE(SUM(CASE WHEN MONTH(DATAEMISSAO) = 1 THEN VALORLIQUIDO  END, 0) AS 'JAN',
         COALESCE(SUM(CASE WHEN MONTH(DATAEMISSAO) = 2 THEN VALORLIQUIDO  END, 0) AS 'FEV',
         COALESCE(SUM(CASE WHEN MONTH(DATAEMISSAO) = 3 THEN VALORLIQUIDO  END, 0) AS 'MAR',
         COALESCE(SUM(CASE WHEN MONTH(DATAEMISSAO) = 4 THEN VALORLIQUIDO  END, 0) AS 'ABR',
         COALESCE(SUM(CASE WHEN MONTH(DATAEMISSAO) = 5 THEN VALORLIQUIDO  END, 0) AS 'MAIO',
         COALESCE(SUM(CASE WHEN MONTH(DATAEMISSAO) = 6 THEN VALORLIQUIDO  END, 0) AS 'JUN',
         COALESCE(SUM(CASE WHEN MONTH(DATAEMISSAO) = 7 THEN VALORLIQUIDO  END, 0) AS 'JUL',
         COALESCE(SUM(CASE WHEN MONTH(DATAEMISSAO) = 8 THEN VALORLIQUIDO  END, 0) AS 'AUG',
         COALESCE(SUM(CASE WHEN MONTH(DATAEMISSAO) = 9 THEN VALORLIQUIDO  END, 0) AS 'SET',
         COALESCE(SUM(CASE WHEN MONTH(DATAEMISSAO) = 10 THEN VALORLIQUIDO END, 0) AS 'OUT',
         COALESCE(SUM(CASE WHEN MONTH(DATAEMISSAO) = 11 THEN VALORLIQUIDO END, 0) AS 'NOV',
         COALESCE(SUM(CASE WHEN MONTH(DATAEMISSAO) = 12 THEN VALORLIQUIDO END, 0) AS 'DEZ'
    FROM   TMOV
    WHERE YEAR(DATAEMISSAO) = '2011'  AND TMOV.CODTMV = '1.2.01'
    GROUP BY YEAR(DATAEMISSAO) ,TMOV.CODCCUSTO
    )
    select *,JAN+FEV+MAR+ABR+MAIO+JUN+JUL+AUG+[SET]+[OUT]+NOV+DEZ as soma, (JAN+FEV+MAR+ABR+MAIO+JUN+JUL+AUG+[SET]+[OUT]+NOV+DEZ)/12 as media from cte_dados
    ORDER BY [YEAR]
    


    Att.
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS.
    Se útil, classifique!!!
    Me siga no twitter: @marcelodba

    sexta-feira, 5 de agosto de 2011 14:05
  • Prezaados Marcelo agora ocorreu esse erro;

     

    Msg 174, Level 15, State 1, Line 6
    The SUM function requires 1 argument(s).

    alguma coisa no SUM, o pode ser agora.. obrigado

    sexta-feira, 5 de agosto de 2011 18:01
  • Ronnie erro na sintaxe :) fiz correndo :)

    agora acho que rola...

    ;with cte_dados
    as(
    
    SELECT  YEAR = YEAR(DATAEMISSAO),
        CODCCUSTO,
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 1 THEN SUM(VALORLIQUIDO) END, 0) AS 'JAN',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 2 THEN SUM(VALORLIQUIDO) END, 0) AS 'FEV',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 3 THEN SUM(VALORLIQUIDO) END, 0) AS 'MAR',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 4 THEN SUM(VALORLIQUIDO) END, 0) AS 'ABR',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 5 THEN SUM(VALORLIQUIDO) END, 0) AS 'MAIO',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 6 THEN SUM(VALORLIQUIDO) END, 0) AS 'JUN',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 7 THEN SUM(VALORLIQUIDO) END, 0) AS 'JUL',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 8 THEN SUM(VALORLIQUIDO) END, 0) AS 'AUG',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 9 THEN SUM(VALORLIQUIDO) END, 0) AS 'SET',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 10 THEN SUM(VALORLIQUIDO) END, 0) AS 'OUT',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 11 THEN SUM(VALORLIQUIDO) END, 0) AS 'NOV',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 12 THEN SUM(VALORLIQUIDO) END, 0) AS 'DEZ'
    FROM   TMOV
    WHERE YEAR(DATAEMISSAO) = '2011'  AND TMOV.CODTMV = '1.2.01'
    GROUP BY YEAR(DATAEMISSAO) ,TMOV.CODCCUSTO
    )
    select *,JAN+FEV+MAR+ABR+MAIO+JUN+JUL+AUG+[SET]+[OUT]+NOV+DEZ as soma, (JAN+FEV+MAR+ABR+MAIO+JUN+JUL+AUG+[SET]+[OUT]+NOV+DEZ)/12 as media from cte_dados
    ORDER BY [YEAR]
    GO
    


    Att.
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS.
    Se útil, classifique!!!
    Me siga no twitter: @marcelodba

    sexta-feira, 5 de agosto de 2011 18:25
  • Marcelo acho que estamos no caminho certo, me retornou as informaçoes, porem nao esta agrupando , o que pode ser..

     

    sexta-feira, 5 de agosto de 2011 19:13
  • Novo Tento

    ;with cte_dados
    as(
    
    SELECT  YEAR = YEAR(DATAEMISSAO),
        CODCCUSTO,
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 1 THEN SUM(VALORLIQUIDO) END, 0) AS 'JAN',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 2 THEN SUM(VALORLIQUIDO) END, 0) AS 'FEV',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 3 THEN SUM(VALORLIQUIDO) END, 0) AS 'MAR',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 4 THEN SUM(VALORLIQUIDO) END, 0) AS 'ABR',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 5 THEN SUM(VALORLIQUIDO) END, 0) AS 'MAIO',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 6 THEN SUM(VALORLIQUIDO) END, 0) AS 'JUN',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 7 THEN SUM(VALORLIQUIDO) END, 0) AS 'JUL',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 8 THEN SUM(VALORLIQUIDO) END, 0) AS 'AUG',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 9 THEN SUM(VALORLIQUIDO) END, 0) AS 'SET',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 10 THEN SUM(VALORLIQUIDO) END, 0) AS 'OUT',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 11 THEN SUM(VALORLIQUIDO) END, 0) AS 'NOV',
         COALESCE(CASE WHEN MONTH(DATAEMISSAO) = 12 THEN SUM(VALORLIQUIDO) END, 0) AS 'DEZ'
    FROM   TMOV
    WHERE YEAR(DATAEMISSAO) = '2011'  AND CODTMV = '1.2.01'
    GROUP BY DATAEMISSAO,CODCCUSTO
    ), cte_soma as (select [YEAR], CODCCUSTO, SUM(JAN) as JAN, SUM(FEV) as FEV, SUM(MAR) as MAR, SUM(ABR) as ABR, SUM(MAIO) as MAIO
    , SUM(JUN) as JUN, SUM(JUL) as JUL, SUM(AUG) as AUG, SUM([SET]) as 'SET', SUM([OUT]) as 'OUT', SUM(NOV) as NOV, SUM(DEZ) as DEZ from cte_dados Group by [YEAR],CODCCUSTO)
    
    select *,JAN+FEV+MAR+ABR+MAIO+JUN+JUL+AUG+[SET]+[OUT]+NOV+DEZ as soma, (JAN+FEV+MAR+ABR+MAIO+JUN+JUL+AUG+[SET]+[OUT]+NOV+DEZ)/12 as media from cte_soma
    ORDER BY [YEAR]
    


    Att
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS.
    Se útil, classifique!!!
    Me siga no twitter: @marcelodba

    • Marcado como Resposta Ronnie Von sexta-feira, 5 de agosto de 2011 20:19
    sexta-feira, 5 de agosto de 2011 19:45
  •  

     

    Parabens Marcelo era isso mesmo que eu queria, ficou muito bom..

     

    sexta-feira, 5 de agosto de 2011 20:20