Sequelize complains "misuse of aggregate: SUM()" when using double vs single quotes in raw query

Question

When I run this query in DB Browser for SQLite:

SELECT week
, SUM (CASE when activityType = 'rockclimbing' then elapsedTime else 0 END) AS rockclimbing
, SUM (CASE when activityType = 'hike' then elapsedTime else 0 END) AS hike
, SUM (CASE when activityType = 'run' then elapsedTime else 0 END) AS run
FROM activity
WHERE year=2016
AND activityType IN ('rockclimbing', 'hike', 'run')
GROUP BY week
ORDER BY week;

It returns the data that I am looking for:

week rockclimbing hike run

"1" "0" "0" "2.09"

"2" "0" "0" "4.63"

"3" "0" "0" "7.22"

"4" "0" "0" "2.94"

"5" "0" "0" "5.59"

(Ignore the "quotes". SO copy/pasted the data with them included.)

However, when I pass this query to Sequelize using .query() I get the error: misuse of aggregate: SUM()

Here is my Sequelize code:

sequelize.query('SELECT week, SUM (CASE when activityType = "rockclimbing" then elapsedTime else 0 END) AS rockclimbing, SUM (CASE when activityType = "hike" then elapsedTime else 0 END) AS hike, SUM (CASE when activityType = "run" then elapsedTime else 0 END) AS run FROM activity WHERE year=2016 AND activityType IN ("rockclimbing", "hike", "run") GROUP BY week ORDER BY week;', { model: Activity }).then(...etc..).catch(...etc...);

Here is the query as generated by Sequelize and output in my console:

Executing (default): SELECT week, SUM (CASE when activityType = "rockclimbing" then elapsedTime else 0 END) AS rockclimbing, SUM (CASE when activityType = "hike" then elapsedTime else 0 END) AS hike, SUM (CASE when activityType = "run" then elapsedTime else 0 END) AS run FROM activity WHERE year=2016 AND activityType IN ("rockclimbing", "hike", "run") GROUP BY week ORDER BY week;

My JavaScript .catch()s errors and logs them to the console:

.catch(function(err) {
  console.log('Error! ', err);
});

So I see this error object logged out to my console:

{ SequelizeDatabaseError: SQLITE_ERROR: misuse of aggregate: SUM()
    at Query.formatError (/xxx/xxx/project_name/node_modules/sequelize/lib/dialects/sqlite/query.js:348:14)
    at afterExecute (/xxx/xxx/project_name/node_modules/sequelize/lib/dialects/sqlite/query.js:112:29)
    at replacement (/xxx/xxx/project_name/node_modules/sqlite3/lib/trace.js:19:31)
    at Statement.errBack (/xxx/xxx/project_name/node_modules/sqlite3/lib/sqlite3.js:16:21)
  name: 'SequelizeDatabaseError',
  message: 'SQLITE_ERROR: misuse of aggregate: SUM()',
  parent: 
   { Error: SQLITE_ERROR: misuse of aggregate: SUM()
       at Error (native)
     errno: 1,
     code: 'SQLITE_ERROR',
     sql: 'SELECT week, SUM (CASE when activityType = "rockclimbing" then elapsedTime else 0 END) AS rockclimbing, SUM (CASE when activityType = "hike" then elapsedTime else 0 END) AS hike, SUM (CASE when activityType = "run" then elapsedTime else 0 END) AS run FROM activity WHERE year=2016 AND activityType IN ("rockclimbing", "hike", "run") GROUP BY week ORDER BY week;' },
  original: 
   { Error: SQLITE_ERROR: misuse of aggregate: SUM()
       at Error (native)
     errno: 1,
     code: 'SQLITE_ERROR',
     sql: 'SELECT week, SUM (CASE when activityType = "rockclimbing" then elapsedTime else 0 END) AS rockclimbing, SUM (CASE when activityType = "hike" then elapsedTime else 0 END) AS hike, SUM (CASE when activityType = "run" then elapsedTime else 0 END) AS run FROM activity WHERE year=2016 AND activityType IN ("rockclimbing", "hike", "run") GROUP BY week ORDER BY week;' },
  sql: 'SELECT week, SUM (CASE when activityType = "rockclimbing" then elapsedTime else 0 END) AS rockclimbing, SUM (CASE when activityType = "hike" then elapsedTime else 0 END) AS hike, SUM (CASE when activityType = "run" then elapsedTime else 0 END) AS run FROM activity WHERE year=2016 AND activityType IN ("rockclimbing", "hike", "run") GROUP BY week ORDER BY week;' }

What is the problem here? Why does this query work in DB Browser but not in my app?

I did search the Internet and Stackoverflow before posting this question. While some questions did touch on my issue none of the suggestions/solutions helped me/worked when I tried them.

Answer 1

There are a few subtle differences here, and one of them is important: I transposed the ' and " characters in the javascript.

> Executing (default): SELECT * FROM `activity`
[ { activityType: 'rockclimbing',
    year: 2017,
    week: 1,
    elapsedTime: 1.2345 },
  { activityType: 'rockclimbing',
    year: 2017,
    week: 2,
    elapsedTime: 1.2345 },
  { activityType: 'rockclimbing',
    year: 2017,
    week: 3,
    elapsedTime: 66.45 },
  { activityType: 'hike', year: 2017, week: 1, elapsedTime: 1.2345 },
  { activityType: 'hike',
    year: 2017,
    week: 2,
    elapsedTime: 3.141592 },
  { activityType: 'hike', year: 2017, week: 3, elapsedTime: -1 },
  { activityType: 'hike', year: 2017, week: 4, elapsedTime: 10000 },
  { activityType: 'run', year: 2017, week: 4, elapsedTime: 10 },
  { activityType: 'run', year: 2017, week: 5, elapsedTime: 100 },
  { activityType: 'run', year: 2017, week: 6, elapsedTime: 1000 } ]

> sequelize.query("SELECT week, SUM (CASE when activityType = 'rockclimbing' then elapsedTime else 0 END) AS rockclimbing, SUM (CASE when activityType = 'hike' then elapsedTime else 0 END) AS hike, SUM (CASE when activityType = 'run' then elapsedTime else 0 END) AS run FROM activity     WHERE year=2017 AND activityType IN ('rockclimbing', 'hike', 'run') GROUP BY week ORDER BY week", { type: sequelize.QueryTypes.SELECT}).then(function(activities) {console.log(activities)})
Promise {
  _bitField: 0,
  _fulfillmentHandler0: undefined,
  _rejectionHandler0: undefined,
  _promise0: undefined,
  _receiver0: undefined }
> Executing (default): SELECT week, SUM (CASE when activityType = 'rockclimbing' then elapsedTime else 0 END) AS rockclimbing, SUM (CASE when activityType = 'hike' then elapsedTime else 0 END) AS hike, SUM (CASE when activityType = 'run' then elapsedTime else 0 END) AS run FROM     activity WHERE year=2017 AND activityType IN ('rockclimbing', 'hike', 'run') GROUP BY week ORDER BY week

Results:

[ { week: 1, rockclimbing: 1.2345, hike: 1.2345, run: 0 },
  { week: 2, rockclimbing: 1.2345, hike: 3.141592, run: 0 },
  { week: 3, rockclimbing: 66.45, hike: -1, run: 0 },
  { week: 4, rockclimbing: 0, hike: 10000, run: 10 },
  { week: 5, rockclimbing: 0, hike: 0, run: 100 },
  { week: 6, rockclimbing: 0, hike: 0, run: 1000 } ]

DDL & DML:

sqlite> create table activity ( activityType varchar(20), year int, week int, elapsedTime float);
sqlite> insert into activity values ('rockclimbing',2017,1,1.2345);
sqlite> insert into activity values ('rockclimbing',2017,2,1.2345);
sqlite> insert into activity values ('rockclimbing',2017,3,66.45);
sqlite> insert into activity values ('hike',2017,1,1.2345);
sqlite> insert into activity values ('hike',2017,2,3.141592);
sqlite> insert into activity values ('hike',2017,3,-1);
sqlite> insert into activity values ('hike',2017,4,10000);
sqlite> insert into activity values ('run',2017,4,10);
sqlite> insert into activity values ('run',2017,5,100);
sqlite> insert into activity values ('run',2017,6,1000);

SQLite is expecting single quotes, and throws the wrong error, it seems.

> sequelize.query('SELECT week, SUM (CASE when activityType = "rockclimbing" then elapsedTime else 0 END) AS rockclimbing, SUM (CASE when activityType = "hike" then elapsedTime else 0 END) AS hike, SUM (CASE when activityType = "run" then elapsedTime else 0 END) AS run FROM activity     WHERE year=2017 AND activityType IN ("rockclimbing", "hike", "run") GROUP BY week ORDER BY week', { type: sequelize.QueryTypes.SELECT}).then(function(activities) {console.log(activities)})
Promise {
  _bitField: 0,
  _fulfillmentHandler0: undefined,
  _rejectionHandler0: undefined,
  _promise0: undefined,
  _receiver0: undefined }
> )Executing (default): SELECT week, SUM (CASE when activityType = "rockclimbing" then elapsedTime else 0 END) AS rockclimbing, SUM (CASE when activityType = "hike" then elapsedTime else 0 END) AS hike, SUM (CASE when activityType = "run" then elapsedTime else 0 END) AS run FROM     activity WHERE year=2017 AND activityType IN ("rockclimbing", "hike", "run") GROUP BY week ORDER BY week
Unhandled rejection SequelizeBaseError: SQLITE_ERROR: misuse of aggregate: SUM()
    at Query.formatError (/Users/bryan/node_modules/sequelize/lib/dialects/sqlite/query.js:348:14)
    at afterExecute (/Users/bryan/node_modules/sequelize/lib/dialects/sqlite/query.js:112:29)
    at Statement.errBack (/Users/bryan/node_modules/sqlite3/lib/sqlite3.js:16:21)