Search code examples
sqlrhadoopimpalarodbc

How do you get sum function working in SQLquery in RODBC

I have a script which works in as an SQL query in Hadoop Impala, however when I try to run in R using the package RODBC the only line creating an error if the "sum" function, as follows:

install.packages("RODBC")
library(RODBC)
conn <- odbcConnect("ODBC Impala")

example1 <- sqlQuery(conn, "
select
    column1 as Name, column2 as Date, sum(column3) as Balance
from 
    database1.table1
where 
    column2="20151130"")

However when i exclude:

sum(column3) as Balance

the script runs just fine. Can anyone help please?

Solution

  • Presumably, you need a group by:

    select column1 as Name, column2 as Date, sum(column3) as Balance
from  database1.table1
where column2 = '20151130'
group by column1, column2;