sqldf gives error while using subquery

Question

I'm trying to get moving average by weekday, for that I'm using sql query. Dataframe is

and sqldf code:

ma_782 =  sqldf("SELECT  
         t1.Id_indicator,   t1.Hour,  
 (  
  select SUM(t2.Value) / COUNT(t2.Value)                                 
    FROM  max_value_782 AS t2  
    WHERE   
        t1.Hour = t2.Hour and  
             weekdays.Date(t1.Date) = weekdays.Date(t2.Date)  
            and DATEDIFF(t1.Date, t2.Date) BETWEEN 1 AND 42        
  ) AS 'MA_by_weekday'  
FROM max_value_782 AS t1  ;")

This gives error

Error in rsqlite_send_query(conn@ptr, statement) : near "(": syntax error

while it works from simple select:

sqldf("select * from max_value_782")

Parfait · Accepted Answer · 2017-05-25 19:36:24Z

2

Consider replacing the weekdays. method as this syntax assumes a table qualifier. Since by default sqldf uses the SQLite dialect, use strftime to compare weekdays. Also, single quotes are used for string literals and not to enclose table/field identifiers. SQLite can uses brackets, backticks, or double quotes, or none if reserved words/special characters are not used.

ma_782 =  sqldf("SELECT  t1.Id_indicator, t1.Hour,  
                         (SELECT AVG(t2.Value)
                          FROM max_value_782 AS t2  
                          WHERE t1.Hour = t2.Hour 
                          AND strftime('%w', t1.Date) = strftime('%w', t2.Date)  
                          AND (t2.Date - t2.Date) BETWEEN 1 AND 42        
                         ) AS MA_by_weekday  
                FROM max_value_782 AS t1;")

answered May 25, 2017 at 19:36

Parfait

108k19 gold badges103 silver badges138 bronze badges

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

2 Comments

G. Grothendieck Over a year ago

Note that the semicolon is not needed.

Parfait Over a year ago

No problem...Glad to help. Happy coding!

Collectives™ on Stack Overflow

sqldf gives error while using subquery

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related