Error: 3504 " Selected non-aggregate values must be part of the associated group"

General

Error: 3504 " Selected non-aggregate values must be part of the associated group"

Hello,

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?

Thank you,

select distinct

mem_id,

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

from MPR1

group by mem_id;

1 REPLY
Senior Apprentice

Re: Error: 3504 " Selected non-aggregate values must be part of the associated group"

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.

Dieter