I try to run this sql statement but I've got an error 3504 which idicates the selected non-aggregate values must be part of the associated group.
Could you please help?
count (*) over (partition by mem_id order by sold_date) as num_fills, --count window--
sum(Day_Supply) as ttldsup,
max(index_date + Day_Supply + max(sold_date + Day_supply)) - Index_date as duration
group by mem_id;
OLAP functions are calculated after aggregation, "order by sold_date" fails because there's no more detail row, but why do you need it at all. When you just want a group count, remove it.
And the max calculation will not run in any existing RDBMs.
And "distinct" is not needed when you do a "group by", both return distinct rows.
Remove the OLAP function and try to get your query running using only group by.