Sequelize Multiple counts for one table

Question

From one column in my table I want to get the sum count for the value types in these columns. As an example, one column are:

|paymentGateway |
---------------
|   Paystack    |
|   Flutterwave |
|   NIBSS       |
|   PAGA        |
|   Interswitch |
|   Paystack    |
|   Flutterwave |
|   NIBSS       |
|   PAGA        |
|   Interswitch |
|   Paystack    |
|   Flutterwave |
|   NIBSS       |
|   PAGA        |
|   Interswitch |

I ran the query in Progress DB Viewer and it works fine. This is the query:

SELECT
  "paymentGateway",
  SUM(1) FILTER (WHERE "paymentGateway" = 'Paystack') AS paystack,
  SUM(1) FILTER (WHERE "paymentGateway" = 'NIBSS') AS nibss,
  SUM(1) FILTER (WHERE "paymentGateway" = 'Flutterwave') AS flutterwave,
  SUM(1) FILTER (WHERE "paymentGateway" = 'Interswitch') AS interswitch,
  SUM(1) FILTER (WHERE "paymentGateway" = 'PAGA') AS paga
FROM
  "Transactions"
GROUP BY
  "paymentGateway"

The above query works fine and gives me this result here:

Now, I'm trying to execute the same query in my code. So, I tried running the raw query first:

db.sequelize.query('SELECT  "paymentGateway",   SUM(1) FILTER (WHERE "paymentGateway" = "Paystack") AS paystack,    SUM(1) FILTER (WHERE "paymentGateway" = "NIBSS") AS nibss,  SUM(1) FILTER (WHERE "paymentGateway" = "Flutterwave") AS flutterwave,  SUM(1) FILTER (WHERE "paymentGateway" = "Interswitch") AS interswitch,  SUM(1) FILTER (WHERE "paymentGateway" = "PAGA") AS paga FROM    "Transactions" GROUP BY     "paymentGateway"').then(data => {
  console.log('Query Result', data)
  return res.status(200).send({ message: 'Completed Successfully' })
}).catch(err => {
  console.log('Query Error: ', err)
  return res.status(200).send({ message: 'Completed Successfully' })
})

Which was giving me SequelizeDatabaseError: column "Paystack" does not exist

I decided to do some googling and read through Sequelize docs. That was where I got this:

Transaction.findAndCountAll({
    attributes: [
      [db.sequelize.fn('COUNT', db.sequelize.col('paymentGateway') === 'NIBSS'), 'nibss'],
      [db.sequelize.fn('COUNT', db.sequelize.col('paymentGateway') === 'Paystack'), 'paystack'],
      [db.sequelize.fn('COUNT', db.sequelize.col('paymentGateway') === 'Flutterwave'), 'flutterwave'],
      [db.sequelize.fn('COUNT', db.sequelize.col('paymentGateway') === 'Interswitch'), 'interswitch'],
      [db.sequelize.fn('COUNT', db.sequelize.col('paymentGateway') === 'PAGA'), 'paga']
    ],
    group: '"paymentGateway"'
  }).then(data => {
    // console.log('Query Result', data)
    console.log('Query Length', data.count)
    console.log('Query Datavalues', data.rows.map(obj => obj.dataValues))
    return res.status(200).send({ message: 'Completed Successfully' })
  }).catch(err => {
    console.log('Query Error: ', err)
    return res.status(200).send({ message: 'Completed Successfully' })
  })

The query above, gave me a result that I understand, but wasn't that meaningful to interact with.

Query Length [ { count: '3940' },
  { count: '3838' },
  { count: '4066' },
  { count: '4092' },
  { count: '4065' } ]
Query Datavalues [ { nibss: '3940',
    paystack: '3940',
    flutterwave: '3940',
    interswitch: '3940',
    paga: '3940' },
  { nibss: '3838',
    paystack: '3838',
    flutterwave: '3838',
    interswitch: '3838',
    paga: '3838' },
  { nibss: '4066',
    paystack: '4066',
    flutterwave: '4066',
    interswitch: '4066',
    paga: '4066' },
  { nibss: '4092',
    paystack: '4092',
    flutterwave: '4092',
    interswitch: '4092',
    paga: '4092' },
  { nibss: '4065',
    paystack: '4065',
    flutterwave: '4065',
    interswitch: '4065',
    paga: '4065' } ]

I would really appreciate it, if anyone can help me understand what I'm doing wrong. Thanks

Answer 1

So, after more googling, I was finally able to solve the problem myself. Below is my implementation:

Transaction.findAll({
  attributes: [
    'paymentGateway',
    [db.sequelize.fn('COUNT', db.sequelize.col('paymentGateway')), 'count']
  ],
  group: 'paymentGateway',
  raw: true,
  logging: true
}).then(data => {
  console.log('Query Result', data)
  return res.status(200).send({ message: 'Completed Successfully' })
})

And the result:

Query Result 
[ 
  { paymentGateway: 'Paystack', count: '3966' },
  { paymentGateway: 'PAGA', count: '3954' },
  { paymentGateway: 'Flutterwave', count: '3995' },
  { paymentGateway: 'Interswitch', count: '4118' },
  { paymentGateway: 'NIBSS', count: '3968' } 
]