I need to display a TOTAL field in a PIVOT query

Asked

Viewed 373 times

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 products are displayed in rows.

Now I need to display in a field (column) the sum of the total amount of each city, how can I do? 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
  • You have indicated the tag of 3 different SGDB, in each can be a syntax... in which database you will use this query?

  • @Camilosantos I will use the query in SQL Server

1 answer

0

I got!

Just make the sum of each city and insert 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'
  • 2

    In the sum expression of cities, it is enough that a single column has no information (i.e., NULL) for the final result to be NULL. // Suggestion: Coalesce(CAMPINAS, 0) + Coalesce(PAULISTA, 0) + ...

  • @Josédiz Maravilha! I will use your suggestion! Thank you very much

Browser other questions tagged

You are not signed in. Login or sign up in order to post.