Error Code: 1064. Trouble with inner join

Question

I am trying to calculate the speed of response: calculate how long it takes from the moment of creating an article with the "IN" type to the moment of creating an article with the "OUT" type. Group all response speed values by categories: less than 1 hour, 1-3 hours, 3-24 hours, 1-3 days, more than 3 days. Count the number of responses in each category. I use MySQL Workbench.

SELECT
dt_cat, COUNT(*) qt
FROM
(SELECT INN.ticketId,
    CASE 
        WHEN DATEDIFF(HOUR, OUT.articleCreatedDt,INN.articleCreatedDt) < 1 THEN 'categ 1'
        WHEN DATEDIFF(HOUR, OUT.articleCreatedDt,INN.articleCreatedDt) in (1, 2) THEN 'categ 2'
        WHEN DATEDIFF(HOUR, OUT.articleCreatedDt,INN.articleCreatedDt) in (3, 24) THEN 'categ 3'
        WHEN DATEDIFF(day, OUT.articleCreatedDt,INN.articleCreatedDt) in (1, 3) THEN 'categ 4'
        WHEN DATEDIFF(day, OUT.articleCreatedDt,INN.articleCreatedDt) > 3 THEN 'categ 5'
    END dt_cat
FROM tickets.articles AS INN
JOIN  tickets.articles AS OUT
ON INN.ticketId = OUT.ticketId) AS t
GROUP BY dt_cat

Error Code: 1064. 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 'OUT ON INN.ticketId = OUT.ticketId) AS t GROUP BY dt_cat' at line 13

`OUT` is a reserved word. Surround it with backticks or use another. — miken32, Feb 28 '21 at 20:21

score 1 · Accepted Answer · answered Feb 28 '21 at 20:22

1

OUT is a reserved MySQL keyword (cf. https://dev.mysql.com/doc/refman/8.0/en/keywords.html). You may use another variable name.

answered Feb 28 '21 at 20:22

pys

91
7

Error Code: 1064. Trouble with inner join

1 Answers1