CODEWITHSUNDEEP
javascriptnode.jssqlitesequelize.js
mjfeintuch
mjfeintuch

Reputation: 29

How can I get the count of rows that equal a certain value while using groupby with Sequelize and SQLite?

I have a table that has email, name, order_number, review. All these fields are text. I am grouping the results by email, and counting how many have an order_number.

The value for the review field is either 'Live', 'Failed' or null. I would like to count how many times each of these values occurs per email address and return the count in a new column (review_live_count, review_failed_count and review_null_count).

Here is my current code:

let users = await db.reservations.findAll({
    attributes: ['email', 'name',[db.sequelize.fn('count', db.sequelize.col('order_number')), 'orders']], group: ["email"],
    raw:true
  });

I am using Node, Sequelize and SQLite. Sequelize and sequelize are both being exposed through the db object.

Upvotes: 1

Views: 719

Answers (1)

Edwin Babu
Edwin Babu

Reputation: 719

Can you try using sequelize literal

let users = await db.reservations.findAll({
attributes: [
  'email', 
  'name',
  [db.sequelize.fn('count', db.sequelize.col('order_number')), 'orders'],
  [db.sequelize.literal(`COUNT (distinct "id") FILTER ( WHERE "review" = 'Live' )` ), 'review_live_count'],
  [db.sequelize.literal(`COUNT (distinct "id") FILTER ( WHERE "review" = 'Failed' )` ), 'review_failed_count'],
  [db.sequelize.literal(`COUNT (distinct "id") FILTER ( WHERE "review" is null )` ), 'review_null_count'],
],
group: ["email"],
raw:true
});

Hope it helps

Upvotes: 2

Related Questions