[SOLVED] LAG and GROUP BY not compatible in Maria DB SQL

LAG and GROUP BY not compatible in Maria DB SQL

I have this SQL query in MariaDB

  SELECT substr(sqlth_te.tagpath, 32), stringvalue,
    ((t_stamp - (CASE WHEN sqlth_te.tagpath = LAG(sqlth_te.tagpath,1) OVER (ORDER BY sqlth_te.tagpath, t_stamp) Then LAG(t_stamp,1) OVER (ORDER BY sqlth_te.tagpath, t_stamp)
    ELSE NULL
    END))/1000) as seconds
    FROM sqlt_data_1_2022_04
    LEFT JOIN sqlth_te
    ON sqlt_data_1_2022_04.tagid = sqlth_te.id
    WHERE stringvalue IS NOT NULL
    ORDER BY sqlth_te.tagpath, t_stamp

sql code

Which returns 3 columns; a column with machine names, running status, and duration since status change.

results

I'd like to sum the duration by machine name and running status, but when I try to add a sum and group by I get an error.

SELECT substr(sqlth_te.tagpath, 32), stringvalue,
SUM((t_stamp - (CASE WHEN sqlth_te.tagpath = LAG(sqlth_te.tagpath,1) OVER (ORDER BY sqlth_te.tagpath, t_stamp) Then LAG(t_stamp,1) OVER (ORDER BY sqlth_te.tagpath, t_stamp)
ELSE NULL
END))/1000) as seconds
FROM sqlt_data_1_2022_04
LEFT JOIN sqlth_te
ON sqlt_data_1_2022_04.tagid = sqlth_te.id
WHERE stringvalue IS NOT NULL
ORDER BY sqlth_te.tagpath, t_stamp
GROUP BY substr(sqlth_te.tagpath, 32), stringvalue

Error: java.sql.SQLSyntaxErrorException: (conn=8) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'GROUP BY substr(sqlth_te.tagpath, 32), stringvalue' at line 10

Any ideas of what I'm doing wrong or if it's possible to group a column generated with the lag function?

Thanks

Solution

First thing: The GROUP BY should come before the ORDER BY

You may also need to nest it like this:

SELECT tagpath, stringvalue, SUM(seconds) as seconds
  FROM (
  SELECT substr(sqlth_te.tagpath, 32) as tagpath, stringvalue,
    ((t_stamp - (CASE WHEN sqlth_te.tagpath = LAG(sqlth_te.tagpath,1) OVER (ORDER BY sqlth_te.tagpath, t_stamp) Then LAG(t_stamp,1) OVER (ORDER BY sqlth_te.tagpath, t_stamp)
    ELSE NULL
    END))/1000) as seconds
    FROM sqlt_data_1_2022_04
    LEFT JOIN sqlth_te
    ON sqlt_data_1_2022_04.tagid = sqlth_te.id
    WHERE stringvalue IS NOT NULL
)
GROUP BY tagpath, stringvalue
ORDER BY tagpath, stringvalue