Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2015
    Posts
    3

    Cool Unanswered: Add column Total at SELECT Pivot

    Dears,

    This SQL Select below doesn't show the total value. Always shows NULL. Why? Someane can help me?

    SELECT Ano,
    FORMAT((QPivot.[1]),'##,##0.00','pt-BR') As trimestre1,
    FORMAT((QPivot.[2]),'##,##0.00','pt-BR') As trimestre2,
    FORMAT((QPivot.[3]),'##,##0.00','pt-BR') As trimestre3,
    FORMAT((QPivot.[4]),'##,##0.00','pt-BR') As trimestre4,
    Municipio,
    Cnpj,
    SUM(QPivot.[1]) + sum(QPivot.[2]) + sum(QPivot.[3]) + sum(QPivot.[4]) AS Total
    FROM (SELECT YEAR(dbo.tab_convenio.Datainicio) [Ano],
    DATEPART(QUARTER, dbo.tab_convenio.Datainicio) [Quarter],
    SUM(dbo.wkf_solic.valor_solicitado) AS Total,dbo.tab_proponente.Municipio as Municipio,dbo.tab_proponente.Cnpj as Cnpj
    FROM dbo.tab_convenio INNER JOIN
    dbo.tab_proponente ON dbo.tab_convenio.ProponenteId = dbo.tab_proponente.Id INNER JOIN
    dbo.wkf_solic ON dbo.tab_convenio.SolicitacaoId = dbo.wkf_solic.wkf_solic_id
    GROUP BY YEAR(dbo.tab_convenio.Datainicio), Municipio, Cnpj,
    DATEPART(QUARTER,dbo.tab_convenio.Datainicio)) AS QuarterlyData
    PIVOT( SUM([Total])
    FOR QUARTER IN ([1],[2],[3],[4])) AS QPivot

    GROUP BY Ano, QPivot.[1],QPivot.[2],QPivot.[3],QPivot.[4], Municipio,Cnpj

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Anything + NULL = NULL

    Look at the individual Sum()s on their own.
    Code:
    SELECT Ano
         , Format((QPivot.[1]),'##,##0.00','pt-BR') As trimestre1
         , Format((QPivot.[2]),'##,##0.00','pt-BR') As trimestre2 
         , Format((QPivot.[3]),'##,##0.00','pt-BR') As trimestre3
         , Format((QPivot.[4]),'##,##0.00','pt-BR') As trimestre4
         , Municipio
         , Cnpj
         , Sum(QPivot.[1])
         , Sum(QPivot.[2])
         , Sum(QPivot.[3])
         , Sum(QPivot.[4])
         , Sum(QPivot.[1]) + Sum(QPivot.[2]) + Sum(QPivot.[3]) + Sum(QPivot.[4]) As Total
    ...
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2015
    Posts
    3

    I solved. thank you

    Quote Originally Posted by gvee View Post
    Anything + NULL = NULL

    Look at the individual Sum()s on their own.
    Code:
    SELECT Ano
         , Format((QPivot.[1]),'##,##0.00','pt-BR') As trimestre1
         , Format((QPivot.[2]),'##,##0.00','pt-BR') As trimestre2 
         , Format((QPivot.[3]),'##,##0.00','pt-BR') As trimestre3
         , Format((QPivot.[4]),'##,##0.00','pt-BR') As trimestre4
         , Municipio
         , Cnpj
         , Sum(QPivot.[1])
         , Sum(QPivot.[2])
         , Sum(QPivot.[3])
         , Sum(QPivot.[4])
         , Sum(QPivot.[1]) + Sum(QPivot.[2]) + Sum(QPivot.[3]) + Sum(QPivot.[4]) As Total
    ...
    Code:
    SELECT YEAR(dbo.tab_convenio.Datainicio) [Ano],
         SUM(case WHEN DATEPART(MONTH, dbo.tab_convenio.Datainicio) BETWEEN 1 AND 3 then dbo.wkf_solic.valor_solicitado END) as "trimestre1",
    	 SUM(case WHEN DATEPART(MONTH, dbo.tab_convenio.Datainicio) BETWEEN 4 AND 6 then dbo.wkf_solic.valor_solicitado END) as "trimestre2",
    	 SUM(case WHEN DATEPART(MONTH, dbo.tab_convenio.Datainicio) BETWEEN 7 AND 9 then dbo.wkf_solic.valor_solicitado END) as "trimestre3",
    	 SUM(case WHEN DATEPART(MONTH, dbo.tab_convenio.Datainicio) BETWEEN 10 AND 12 then dbo.wkf_solic.valor_solicitado END) as "trimestre4",	 
         FORMAT(SUM(dbo.wkf_solic.valor_solicitado),'##,##0.00','pt-BR') AS Total,dbo.tab_proponente.Municipio as Municipio,
    	 dbo.tab_proponente.Cnpj as Cnpj
         FROM dbo.tab_convenio INNER JOIN
         dbo.tab_proponente ON dbo.tab_convenio.ProponenteId = dbo.tab_proponente.Id INNER JOIN
         dbo.wkf_solic ON dbo.tab_convenio.SolicitacaoId = dbo.wkf_solic.wkf_solic_id	     
    
    
    WHERE
    		   ((Cnpj LIKE '%' + @cnpj + '%') OR (@cnpj IS NULL)) AND 
    		   ((Municipio LIKE '%' + @municipio + '%') OR (@municipio IS NULL)) AND
    		   ((YEAR(dbo.tab_convenio.Datainicio) LIKE '%' + @ano + '%') OR (@ano IS NULL))
    		   
    GROUP BY Municipio,Cnpj, YEAR(dbo.tab_convenio.Datainicio)

  4. #4
    Join Date
    Feb 2015
    Posts
    3

    I solved. thank you

    Code:
    SELECT YEAR(dbo.tab_convenio.Datainicio) [Ano],
         SUM(case WHEN DATEPART(MONTH, dbo.tab_convenio.Datainicio) BETWEEN 1 AND 3 then dbo.wkf_solic.valor_solicitado END) as "trimestre1",
    	 SUM(case WHEN DATEPART(MONTH, dbo.tab_convenio.Datainicio) BETWEEN 4 AND 6 then dbo.wkf_solic.valor_solicitado END) as "trimestre2",
    	 SUM(case WHEN DATEPART(MONTH, dbo.tab_convenio.Datainicio) BETWEEN 7 AND 9 then dbo.wkf_solic.valor_solicitado END) as "trimestre3",
    	 SUM(case WHEN DATEPART(MONTH, dbo.tab_convenio.Datainicio) BETWEEN 10 AND 12 then dbo.wkf_solic.valor_solicitado END) as "trimestre4",	 
         FORMAT(SUM(dbo.wkf_solic.valor_solicitado),'##,##0.00','pt-BR') AS Total,dbo.tab_proponente.Municipio as Municipio,
    	 dbo.tab_proponente.Cnpj as Cnpj
         FROM dbo.tab_convenio INNER JOIN
         dbo.tab_proponente ON dbo.tab_convenio.ProponenteId = dbo.tab_proponente.Id INNER JOIN
         dbo.wkf_solic ON dbo.tab_convenio.SolicitacaoId = dbo.wkf_solic.wkf_solic_id	     
    
    
    WHERE
    		   ((Cnpj LIKE '%' + @cnpj + '%') OR (@cnpj IS NULL)) AND 
    		   ((Municipio LIKE '%' + @municipio + '%') OR (@municipio IS NULL)) AND
    		   ((YEAR(dbo.tab_convenio.Datainicio) LIKE '%' + @ano + '%') OR (@ano IS NULL))
    		   
    GROUP BY Municipio,Cnpj, YEAR(dbo.tab_convenio.Datainicio)

  5. #5
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Disregard Thanks
    Last edited by VLOOKUP; 02-26-15 at 16:57.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •