Search code examples
sqlpostgresqlcalculated-columnsalter-table

How to add query results to table in SQL?


I am trying to add the results of a query to an existing table, dependent on the values of an existing column. For example, using the table below

Store Sales Weekday
10 11000 Weekday
11 5000 Weekday
12 8000 Weekday
10 19000 Weekend
11 20000 Weekend
12 5000 Weekend

I want the averages per store and weekday which I can get using the following:

SELECT AVG(Sales) AS weekday_avg, store
FROM store_sales
WHERE Weekday = 'Weekday'
GROUP BY store;

But then I'd like to add these results to the same table and store in a column named 'weekday_avg'.

I've tried the following, and while I don't get an error, the column doesn't have any values added:

ALTER TABLE store_sales ADD COLUMN weekday_avg numeric;
UPDATE store_sales SET weekday_avg = (
    SELECT AVG(Sales) AS weekday_avg
    FROM store_sales
    WHERE Weekday = 'Weekday'
    GROUP BY store
);

I know this probably isn't best database practice, but I'm working with what has been provided and all I need is to end up with a table with columns for averages per store / weekday type that I can export into R for further analysis.

Many thanks in advance!


Solution

  • As mentioned in other answer, don't store calculated values in table.

    But if you want to know how it can be done, then one of the options is to use the corelated query as follows:

    UPDATE store_sales s SET s.weekday_avg = (
        SELECT AVG(ss.Sales) AS weekday_avg
        FROM store_sales ss
        WHERE s.Weekday = ss.weekday
          And ss.store=s.store
    );