CODEWITHSUNDEEP
sqlpostgresqlknex.js
nicholaswmin
nicholaswmin

Reputation: 22949

Get sum as an additional column instead of only column

I'd like to sum up all the order amounts, grouped per user from my database, for a specific set of users.

I'm using .sum() and .groupBy() to do this, like so:

knex('orders')
  .select(['id_user', 'order_amount'])
  .whereIn('id_user', ['user-foo', 'user-bar'])
  .sum('order_amount')
  .groupBy('id_user')

This returns the sums:

[ { sum: 500 } ]
[ { sum: 600 } ]

But now there's no way to know which sum corresponds to which user.

This would be my ideal result:

[ { id_user: 'user-foo', sum: 500 } ]
[ { id_user: 'user-bar', sum: 600 } ]

How can I also get the id_user column for each sum?

Upvotes: 0

Views: 161

Answers (1)

richyen
richyen

Reputation: 9968

You'll need to use knex.raw() for that:

knex.select('id_user', knex.raw('SUM(order_amount)')).from('orders').groupBy('id_user');

Upvotes: 2

Related Questions