I need to display a TOTAL field in a PIVOT query

0

I have the query below that returns the total quantity in stock of the products for each city. Each city is a column and the products are displayed in rows.

Now I need to display in a field (column) the sum of the total quantity of each city, how can I do it? I need a "Total" column and below it the total of each product (which is the sum of the total of all cities).

SELECT id_product
   ,code
   ,product
   ,[CAMPINAS] AS 'campinas'
   ,[PAULISTA] AS 'paulista'
   ,[BELÉM PA] AS 'belem'
   ,[PORTO ALEGRE] AS 'porto_alegre'
   ,[SALVADOR] AS 'salvador'
   ,[RIO DE JANEIRO] AS 'rio_de_janeiro'
   ,[RECIFE] AS 'recife'
   ,[CURITIBA] AS 'curitiba'
   ,[MANAUS] AS 'manaus'
   ,[JOÃO PESSOA] AS 'joao_pessoa'
   ,[MACEIÓ] AS 'maceio'
   ,[BRASILIA] AS 'brasilia'
   ,[FEIRA DE SANTANA] AS 'feira_de_santana'
   ,[SANTOS] AS 'santos'
   ,[BELO HORIZONTE] AS 'belo_horizonte'
   ,[FORTALEZA] AS 'fortaleza'
FROM (
SELECT id_product
    ,code
    ,product
    ,franchise
    ,quantity
FROM VW_ViewPosicaoEstoque
WHERE id_franchise IN 
        (
        SELECT id
        FROM t_franchises
        WHERE id = 1
            OR enum_franchise_user_type = 2
        )
     ) C
PIVOT(AVG(quantity) FOR franchise IN (
        [CAMPINAS]
        ,[PAULISTA]
        ,[BELÉM PA]
        ,[PORTO ALEGRE]
        ,[SALVADOR]
        ,[RIO DE JANEIRO]
        ,[RECIFE]
        ,[CURITIBA]
        ,[MANAUS]
        ,[JOÃO PESSOA]
        ,[MACEIÓ]
        ,[BRASILIA]
        ,[FEIRA DE SANTANA]
        ,[SANTOS]
        ,[BELO HORIZONTE]
        ,[FORTALEZA]
        )) AS P
GO
    
asked by anonymous 15.02.2017 / 17:54

1 answer

0

I did it!

Just do the sum of each city and insert it anywhere before FROM:

,[CAMPINAS] + [PAULISTA] + [BELÉM PA] + [PORTO ALEGRE] + [SALVADOR] + [RIO DE JANEIRO] + 
[RECIFE] + [CURITIBA] + [MANAUS] + [JOÃO PESSOA] + [MACEIÓ] + [BRASILIA] +
[FEIRA DE SANTANA] + [SANTOS] + [BELO HORIZONTE] + [FORTALEZA] AS 'Total'
    
15.02.2017 / 18:29