On Monday, May 16, 2016, Guyren Howe <
guyren@gmail.com> wrote:
I have this SELECT clause as part of a larger query:
FIRST_VALUE(drs.id) OVER (PARTITION BY drs.order_ID ORDER BY drs.position ASC) AS current_drs_id
Seems reasonable to me: group and sort the fields in this table and give me the first value.
But I get "column "
drs.id" must appear in the GROUP BY clause or be used in an aggregate function".
Huh?
The larger query would help...
First_value as shown is in a window expression. It's surrounding query must also have a group by on it. Fields in a windowed function are not considered aggregated for purposes of group by.
Select day_of_month, sum(sales), sum(sum(sales)) over ()
From ...
Group by day_of_month;
For each day of month give me total sales. I also want to see the total sales over the whole query. I need to sum together the individual daily sums. You seem to have a similar situation in your query. The reasoning of first_value is not easy to speculate upon though.
David J.
