Cannot use Count(*) in Lag()

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?

select CreateDate as date, count(*) as count,
       lag(count(*), 1) over(order by CreateDate) as previous 
from contacts
group by createdate

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;