Using WITH clause and INSERT statement in POSTGRESQL

Question

There was a question asked several years ago with a similar title (Using WITH clause with INSERT statement in POSTGRESQL), but I need to figure out a more complicated situation.

I've used the example from POSTGRESQL for using a "With" statement (https://www.postgresql.org/docs/current/queries-with.html). Let's say I made a pre-computed table and wanted to insert the output from the query into it (top_region, total_sales, product_units, and product_sales), how would I do that?

Precomputed table

INSERT INTO top (top_region, total_sales, product_units, product_sales)
select top_region, total_sales, product_units, product_sales
from #not sure here

WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

My query

WITH sales_data AS (
                    SELECT s.customer_id, s.product_id, s.quantity, pr.list_price, pr.category_id, c.state_id
                    FROM order_products s
                    INNER JOIN Products pr ON (s.product_id = pr.product_id)
                    INNER JOIN Customer c ON (s.customer_id = c.customer_id)
                ),
                t_20cat AS (
                    SELECT category_id, ROW_NUMBER() OVER (ORDER BY SUM(list_price*quantity) DESC) AS "rank",  SUM(list_price*quantity) AS total_sales FROM sales_data
                    GROUP BY category_id  ORDER BY total_sales DESC LIMIT 20
                ),
                t_20cust AS (
                    SELECT customer_id, ROW_NUMBER() OVER (ORDER BY SUM(list_price*quantity) DESC) AS "rank", SUM(list_price*quantity) AS total_sales FROM sales_data
                    GROUP BY customer_id  ORDER BY total_sales DESC LIMIT 20
                ),
                t_20cc AS (
                    SELECT customer_id, t_20cust.rank as customer_rank, category_id, t_20cat.rank as category_rank FROM t_20cat, t_20cust
                )
                SELECT t_20cc.*, COALESCE(SUM(sales_data.quantity), 0) AS quantity_sold, COALESCE(SUM(sales_data.list_price*sales_data.quantity), 0) AS dollar_value
                FROM t_20cc
                    LEFT JOIN sales_data ON (
                        t_20cc.customer_id = sales_data.customer_id AND t_20cc.category_id = sales_data.category_id
                    )
                GROUP BY t_20cc.customer_id, t_20cc.customer_rank, t_20cc.category_id, t_20cc.category_rank

INSERT INTO top_20(customer_id, customer_rank, category_id, category_rank
select category_id, category_rank, category_id, category_rank
from t_20cc
)

Dai · Accepted Answer · 2022-03-17 04:00:11Z

1

Move the other SELECT query into its own CTE (region_summary), then reference that in the INSERT statement's SELECT ... FROM clause:

WITH regional_sales AS (
    SELECT
        region,
        SUM(amount) AS total_sales
    FROM
        orders
    GROUP BY
        region
),
top_regions AS (
    SELECT
        region
    FROM
        regional_sales
    WHERE
        total_sales > ( SELECT SUM( total_sales ) / 10 FROM regional_sales )
),
region_summary AS (
    SELECT
        region,
        product,
        SUM(quantity) AS product_units,
        SUM(amount) AS product_sales
    FROM
        orders
    WHERE
        region IN ( SELECT region FROM top_regions )
    GROUP BY
        region,
        product
)
INSERT INTO top ( top_region, total_sales, product_units, product_sales )
SELECT
    region AS top_region,
    product AS total_sales,
    product_units,
    product_sales
FROM
    region_summary;

answered Mar 17, 2022 at 4:00

Dai

157k31 gold badges314 silver badges436 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

James Over a year ago

Thanks, when I try that with my own data, I get an error: "syntax error at or near "INSERT." I've included my query in my question.

Dai Over a year ago

@James Your bottommost SELECT needs to be named CTE too.

James Over a year ago

Yeah, I think that's what I'm confused about...should it be "select customer_id, t_20cust.rank as customer_rank, category_id, t_20cat.rank as category_rank"

James Over a year ago

and include these in from t_20cc, t_20cat, t_20cust?

James Over a year ago

or doing something like this:

total AS (                     SELECT t_20cc.*, ...                 GROUP BY t_20cc.customer_id, t_20cc.customer_rank, t_20cc.category_id, t_20cc.category_rank ) INSERT INTO top_20( customer_id, customer_rank, category_id, category_rank) select customer_id, customer_rank, category_id, category_rank from total;

Collectives™ on Stack Overflow

Using WITH clause and INSERT statement in POSTGRESQL

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related