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!
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
);