RMySQL: SQL Syntax error unidentifiable

Question

This is continuous from my previous question. I'm using RMySQL package in R to send a SQL suery to the MySQL database. The error says something is wrong with the syntax, but I cannot find the error part. Could anybody help me fix this error?

The data table looks like this:

organisation    Tour_ID             A           B           C           D  
Ikea                  a    2018-04-01  2018-05-07  2018-05-09  2018-05-01
Ikea                  a    2018-06-01  2018-05-03  2018-05-29          NA   
Ikea                  a    2018-04-02  2018-05-01  2018-07-08  2018-05-26 
Ikea                  b    2018-06-02  2018-05-01          NA  2018-05-26
Ikea                  b    2018-06-02  2018-05-01          NA  2018-05-26
Ikea                  b            NA  2018-05-05  2018-08-02  2018-06-01
Ikea                  c    2018-06-01  2018-05-07  2018-05-09  2018-05-01
Ikea                  c    2018-06-01  2018-05-03          NA          NA   
Ikea                  c    2018-08-02  2018-05-09  2018-07-08  2018-05-26

And the syntax I wrote is:

cond <- "SELECT t.* FROM myTable t JOIN  
                                   (SELECT organisation, Tour_ID, 
                                    FROM myTable WHERE organisation LIKE "Ikea"  
                                    GROUP BY organisation, Tour_ID 
                                    HAVING A >= \"2018-05-01 00:00\" AND 
                                           A < \"2018-05-31 00:00 \"
                                   ) tt ON 
                                     tt.Tour_ID = t.Tour_ID AND 
                                     tt.organisation = t.organisation"

dbGetQuery(conn = connection, statement = cond)

And the error message I get is:

Unknown column 'A' in 'having clause'

But I see no issue at HAVING part! What's wrong with my syntax? Also if I add A in SELECT clause, the error message reads:

Error in .local(conn, statement, ...) : 
could not run statement: Expression #3 of SELECT list is not in GROUP BY clause 
and contains nonaggregated column 'myTable.myTable.A' 
which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by

Gordon Linoff · Accepted Answer · 2018-06-29 14:52:16Z

1

It is unclear what you want. But columns in the HAVING have to either be in the GROUP BY or arguments to aggregations. Perhaps you want:

(SELECT organisation, Tour_ID
 FROM myTable WHERE organisation LIKE "Ikea"  
 GROUP BY organisation, Tour_ID 
 HAVING MIN(A) >= '2018-05-01' AND 
        MAX(A) < '2018-05-31'
) tt

Single quotes should be used for date constants. I speculate that the second constant should be '2018-06-01', if you want a range for all dates in May.

answered Jun 29, 2018 at 14:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

RMySQL: SQL Syntax error unidentifiable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related