I have a simple query but it keeps throwing “Invalid use of group function”. It works fine when I remove “count(*)”. How can I get the count without using it in lag?
x
select CreateDate as date, count(*) as count,
lag(count(*), 1) over(order by CreateDate) as previous
from contacts
group by createdate
Advertisement
Answer
Hmmm . . . MySQL should allow the use of aggregation functions with window functions. Maybe there is a bug in the parser though.
I think this will work:
select d.*, lag(cnt) over (order by cnt) as previous
from (select CreateDate as date, count(*) as cnt
from contacts
group by CreateDate
) d;