Column "f.price" must appear in the GROUP BY clause or be used in an aggregate function, but I've already used window function

Question

I have a table with two columns: date and price. They both aren't unique.

I need to get running total in unique date order (one date - values sum for this date, next date - next sum + previous one and so on).

I know how to do this with subquery, but I want to use window functions:

There is a simple query:

SELECT f.date, SUM(f.price) OVER () FROM f GROUP BY f.date

It returns the error:

column f.price must appear in the GROUP BY clause or be used in an aggregate function

But I've already used aggregate function (SUM).

Can somebody tell me why this happend?

Though this answer is MySQL, I would think the solution you're looking for would be very similar: https://stackoverflow.com/questions/664700/calculate-a-running-total-in-mysql — WOUNDEDStevenJones, Mar 28 '19 at 20:51
The 3d anwer is really close, but I've had the error and he hasn't. — Acid Ascorbic, Mar 28 '19 at 21:20

score 0 · Answer 1 · answered Mar 28 '19 at 20:14

0

try avoiding over()

select f.date,
   SUM(f.price) 
from f
group by f.date

answered Mar 28 '19 at 20:14

ScaisEdge

131,976
10
91
107

In this case I'll get sum for one day without adding the result to the next day result. I need running total. – Acid Ascorbic Mar 28 '19 at 20:29
you are using a empty over .. .. try using an over with a proper content .. .. anyway seems that error iis solved .. – ScaisEdge Mar 28 '19 at 20:31

score 0 · Answer 2 · answered Mar 28 '19 at 22:11

You are mixing window functions and aggregation, which is generally not a good idea. You are getting the error because, indeed, column f.price is not used in an aggregate function (it is used a window function).

I believe that the following query should give you what you want. It uses a window function, and relies on DISTINCT instead of aggregation.

SELECT DISTINCT fdate, SUM(fprice) OVER(ORDER BY fdate) FROM f ORDER BY fdate;

Demo on DB Fiddle:

Consider the following sample data, that seems to match your spec:

| fdate                    | fprice |
| ------------------------ | ------ |
| 2018-01-01T00:00:00.000Z | 1      |
| 2018-01-01T00:00:00.000Z | 2      |
| 2018-01-02T00:00:00.000Z | 3      |
| 2018-01-03T00:00:00.000Z | 4      |
| 2018-01-03T00:00:00.000Z | 1      |

The query would return:

| fdate                    | sum |
| ------------------------ | --- |
| 2018-01-01T00:00:00.000Z | 3   |
| 2018-01-02T00:00:00.000Z | 6   |
| 2018-01-03T00:00:00.000Z | 11  |

Column "f.price" must appear in the GROUP BY clause or be used in an aggregate function, but I've already used window function

2 Answers2