Question 183784 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Error while running SQL query, window functions

I am trying to find the YoY growth of the users and trying to write a mysql query using window functions.

Below is my query to find the lag :

select substr(create_time, 1, 4) as date,
       count(*) as count,
       lag(count(*), 1) over substr(create_time, 1, 4)
from test_table
group by 1
order by 1

Note: create_time column looks like: 2019-03-29 20:12:03, so I’m using the substr to get the year.

Error:

[Code: 1064, SQL State: 42000] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘substr(create_time, 1, 4)
from test_table
group by 1
order by 1’ at line 3 [Script position: 398 – 453]

I am debugging it for the past 2 hours, what I am doing wrong here?

Thanks in advance

The expected result should be a response with a column lag.

Tags:

2

Answers


  1. 0

    ‘group by’ and ‘order by’ should follow by column name.

    Login or Signup to reply.
  2. 0

    Can you try adding braces and PARTITION BY or ORDER BY:

    lag(count(*), 1) over (ORDER BY substr(create_time, 1, 4))


    WITH X AS
(
  select substr(create_time, 1, 4) as date,
         count(*) as countX
  from test_table
  group by 1
)
Select 
      Date,
      lag(countX, 1) over (ORDER BY date)
From X
order by 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search