SELECT *
FROM 
    (SELECT *
     FROM salestotal) x
PIVOT
    (SUM(sales)
        FOR product_id IN ([1256], [4437], [7684])
    ) p

This the code that I tried. SQL fiddle says that something wrong with the code.

the table for reference

the fiddle

CodePudding user response：

To get the sum(sales) per product_id :

SELECT product_id, sum(sales) 
  FROM sales_totals
 GROUP BY product_id

And joining with the product table columns :

SELECT p.*, s.total_sales
  FROM 
     ( SELECT product_id, sum(sales) AS total_sales
         FROM sales_totals
        GROUP BY product_id
     ) AS s
 INNER JOIN product AS p
    ON s.product_id = p.product_id

What do you need in addition ?

CodePudding user response：

Just an FYI, PIVOT function doesn't exist in Postgres. You may try to use crosstab function of tablefunc module, which provides the same functionality.

Below are two other ways of doing it.

SELECT SUM(CASE WHEN product_id = 1256 THEN sales ELSE 0 END) AS "1256",
       SUM(CASE WHEN product_id = 4437 THEN sales ELSE 0 END) AS "4437",
       SUM(CASE WHEN product_id = 7684 THEN sales ELSE 0 END) AS "7684" 
FROM sales_totals

SELECT SUM(sales) FILTER (WHERE product_id = 1256) AS "1256",
       SUM(sales) FILTER (WHERE product_id = 4437) AS "4437",
       SUM(sales) FILTER (WHERE product_id = 7684) AS "7684" 
FROM sales_totals