postgresql - Add an extra WHERE clause to a working query to further sort by percentage

Question

Need to be able to populate the PERCENT_OF_TOTAL_ORDER column, with a WHERE filter on STORE_LOCATION.

I had some great answers to the first question around parsing the data, and learned how better to describe my request here: In Postgres, how do I COUNT and WHERE in the same query, then do math on result?

So the second part of the request is to be able to now write the data back to the PERCENT_OF_TOTAL_ORDER column using the data in the STORE_LOCATION field, based on the PERCENT_OF_TOTAL_ORDER being . NOTE: Store location will ONLY be north or south.

The current state of the DB looks like this: http://prntscr.com/m3ktu4 . There are several <NULL> entries in the PERCENT_OF_TOTAL_ORDER column, while some are filled in. That is the column I'll be wanting to populate. So I think I want to do a SELECT statement and WHERE clause to SET the PERCENT_OF_TOTAL.

This actual table is currently ~500k+ rows, with ~50k different ORDER_NUMBERs so looking for the most efficient code to update the current data, and then plan to set the code as a CRON job to update nightly going forward.

From the previous post, mentioned above, both these 2 solutions get the % correct, but do not populate the PERCENT_OF_TOTAL_ORDER column.

Solution1:

    SELECT selling_agent, order_number,
           sum(sale_price_1) as sale_price_1,
           sum(sale_price_2) as sale_price_2,
           (sum(sale_price_1) + sum(sale_price_2)) * 100.00 / sum(sum(sale_price_1) + sum(sale_price_2)) over (partition by order_number) as orderPercentage
    FROM sales_orders_test
    GROUP by order_number, selling_agent
    ORDER BY order_number, selling_agent

Solution 2:

    SELECT s.*,
    ROUND (100.00 * (s.sale_price_1 + s.sale_price_2) /
    (SELECT
    SUM(sale_price_1 + sale_price_2)
    FROM sales_orders_test
    WHERE order_number = s.order_Number), 2) percentage
    FROM sales_orders_test s;

I tried to add a WHERE clause on Solution 2 with this:

    UPDATE sales_orders_test
    SET percent_of_total_order =
    (
    SELECT selling_agent, order_number,
    sum(sale_price_1) as sale_price_1,
    sum(sale_price_2) as sale_price_2,
    (sum(sale_price_1) + sum(sale_price_2)) * 100.00 /
    sum(sum(sale_price_1) + sum(sale_price_2)) over (partition by order_number) as orderPercentage
    FROM sales_orders_test
    GROUP by order_number, selling_agent
    ORDER BY order_number, selling_agent
    )

    WHERE percent_of_total_order IS NULL

...and it gave me [42601] ERROR: subquery must return only one column

How can I get the percentage into the column, based on the STORE_LOCATION, to be updated based on this info?

The planned output is the table being populated this one time, then nightly, with an end result like this: http://prntscr.com/m3l3fz

EDIT: @used_by_already's suggestion (thank you), here is the DB as it looks now:

    +-------+---------------+--------------+--------------+-------------+------------------------+----------------+
    | pk_id | selling_agent | order_number | sale_price_1 |         sale_price_2 | percent_of_total_order | store_location |
    +-------+---------------+--------------+--------------+--------------+------------------------+----------------+
    |     1 | jim           |          123 |            1 |                    2 |                        | south          |
    |     2 | steve         |          123 |            1 |                    3 |                        | south          |
    |     3 | carl          |          123 |            1 |                    4 |                        | north          |
    |     4 | carl          |          456 |            1 |                    5 |                        | north          |
    |     5 | steve         |          456 |            1 |                    5 |                        | north          |
    |     6 | jim           |          456 |            1 |                    6 |                  36.84 | north          |
    |     7 | steve         |          789 |            1 |                   78 |                        | south          |
    |     8 | patty         |          789 |            1 |                    7 |                        | north          |
    |     9 | bob           |          187 |            3 |                    3 |                    100 | south          |
    +-------+---------------+--------------+--------------+-----------        ---+------------------------+----------------+

Here is the code as I'd like it to look:

    +-------+---------------+--------------+--------------+-------------+------------------------+----------------+
    | pk_id | selling_agent | order_number | sale_price_1 |         sale_price_2 | percent_of_total_order | store_location |
    +-------+---------------+--------------+--------------+--------------+------------------------+----------------+
    |     1 | jim           |          123 |            1 |                    2 |                  42.86 | south          |
    |     2 | steve         |          123 |            1 |                    3 |                  57.14 | south          |
    |     3 | carl          |          123 |            1 |                    4 |                  100   | north          |
    |     4 | carl          |          456 |            1 |                    5 |                  27.27 | north          |
    |     5 | steve         |          456 |            1 |                    5 |                  40.91 | north          |
    |     6 | jim           |          456 |            1 |                    6 |                  31.82 | north          |
    |     7 | steve         |          789 |            1 |                   78 |                  100   | south          |
    |     8 | patty         |          789 |            1 |                    7 |                  100   | north          |
    |     9 | bob           |          187 |            3 |                    3 |                  100   | south          |
    +-------+---------------+--------------+--------------+-----------        ---+------------------------+----------------+

Answer 1

I got it! So I figured I'd share it here!

    WITH perc_sales as (


SELECT pk_id,
       selling_agent,
       order_number,
       store_location,
       sum(sale_price_1)                                                           as Sale1,
       sum(sale_price_2)                                                           as Sale2,
       ROUND((sum(sale_price_1) + sum(sale_price_2)) * 100.00 /
             GREATEST(sum(sum(sale_price_1) + sum(sale_price_2))
                          over (partition by order_number, store_location), 1), 2) as orderPercentage
FROM sales_orders_test
GROUP BY pk_id
)
UPDATE sales_orders_test AS PERC_UPDATE
SET percent_of_total_order = PS.orderPercentage
FROM perc_sales PS
WHERE PS.pk_id = PERC_UPDATE.pk_id;

That gives me:

    +-------+---------------+--------------+--------------+--------------+------------------------+----------------+
| pk_id | selling_agent | order_number | sale_price_1 | sale_price_2 | percent_of_total_order | store_location |
+-------+---------------+--------------+--------------+--------------+------------------------+----------------+
|     1 | jim           |          123 |            1 |            2 |                  42.86 | south          |
|     2 | steve         |          123 |            1 |            3 |                  57.14 | south          |
|     3 | carl          |          123 |            1 |            4 |                    100 | north          |
|     4 | carl          |          456 |            1 |            5 |                  27.27 | north          |
|     5 | steve         |          456 |            1 |            8 |                  40.91 | north          |
|     6 | jim           |          456 |            1 |            6 |                  31.82 | north          |
|     7 | steve         |          789 |            1 |           78 |                    100 | south          |
|     8 | patty         |          789 |            1 |            7 |                    100 | north          |
|     9 | bob           |          187 |            3 |            3 |                    100 | south          |
+-------+---------------+--------------+--------------+--------------+------------------------+----------------+

Thanks all for the tips!