Issues with SQL window function- error that column must be aggregated or in group by-CodePudding

I have a table called "sales" with two columns: transaction_date, and transaction_amount: VALUES ('2020-01-16 00:05:54.000000', '122.02'), ('2020-01-07 20:53:04.000000', '1240.00') I want to find the 3-day moving average for each day in January 2020. I am returning the error that transaction_amount must be included in either an aggregated function or in the group by. It does not make sense to group by it, as I only want one entry per day in the resulting table. In my code, I already have the amount in the aggregate function SUM, so I am not sure what else to try. Here is my query so far:

SELECT EXTRACT(DAY FROM transaction_time) AS Jan20_day, SUM(transaction_amount), SUM(transaction_amount) OVER(ORDER BY EXTRACT(DAY FROM transaction_time) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_average FROM sales WHERE EXTRACT(MONTH FROM transaction_time)=1 AND EXTRACT(YEAR FROM transaction_time)=2020 GROUP BY EXTRACT(DAY FROM transaction_time)

Any insight on why I am returning the following error?

Query Error: error: column "transactions.transaction_amount" must appear in the GROUP BY clause or be used in an aggregate function

CodePudding user response：

I would expect something like this:

SELECT EXTRACT(DAY FROM transaction_time) AS Jan20_day, 
       SUM(transaction_amount),
       SUM(SUM(transaction_amount)) OVER (ORDER BY EXTRACT(DAY FROM transaction_time) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_average
FROM sales
WHERE transaction_time >= DATE '2020-01-01' AND
      transaction_time < DATE '2020-02-01'
GROUP BY EXTRACT(DAY FROM transaction_time);

But the basic issue with your query is that you need to apply the window function to SUM(), so SUM(SUM(transaction_amount)) . . . .

CodePudding user response：

There is need to use GroupBy Before Where Clause

GROUP BY clause is used with the SELECT statement. In the query,
GROUP BY clause is placed after the WHERE clause. In the query,
GROUP BY clause is placed before ORDER BY clause if used any.

SELECT EXTRACT(DAY FROM transaction_time) AS Jan20_day, SUM(transaction_amount), 
SUM(transaction_amount) 
OVER(ORDER BY EXTRACT(DAY FROM transaction_time) 
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_average 
FROM sales 
WHERE EXTRACT(MONTH FROM transaction_time)=1 
GROUP BY {property}
AND EXTRACT(YEAR FROM transaction_time)=2020 GROUP BY EXTRACT(DAY FROM transaction_time)