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