node.js - Referencing an aliased column in the same select statement with Knex/Sqlite

Question

I'm relatively new to SQL and have it a wall. I'm using Knex with Sqlite and I have a table that's comprised of all sales and it contains the sale amount, the salesperson, and the product category. I'd like to have a statement that gets this information, but then creates another column that is the percentage difference between the sales amount of all sales and the sales of that category.

So far, I have this working fine:

const result = await knex('tablename')
  .select('salesperson')
  .sum('salesvolume')
  .select(
    knex.raw("sum(CASE category = 'myCategory' THEN salesvolume ELSE 0 END) AS categoryvolume"),
  )
  .groupBy('salesperson')
  .orderBy('salesvolume');
// example of result:
// [
//   {
//     salesperson: 'Jim A',
//     salesvolume: 1000,
//     categoryvolume: 400,
//   },
//   {
//     salesperson: 'Sarah B',
//     salesvolume: 200,
//     categoryvolume: 50,
//   }
// ];

In an ideal world, I'd be able to simply add another raw call in my select chain, something like:

const result = await knex('tablename')
  .select('salesperson')
  .sum('salesvolume')
  .select(
    knex.raw("sum(CASE category = 'myCategory' THEN salevolume ELSE 0 END) AS categoryvolume"),
    knex.raw('(sum(categoryvolume) / sum(salesvolume) AS categorypercentage'),
  )
  .groupBy('salesperson')
  .orderBy('salevolume');
// I'm aiming to get:
// [
//   {
//     salesperson: 'Jim A',
//     salesvolume: 1000,
//     categoryvolume: 400,
//     categorypercentage: .4,
//   },
// ];

But that's not going to work since categoryvolume is a column that is aliased and created in the same select statement and can not be referenced. Is there another way I can go about this? I'm feeling like I just am not thinking about it correctly.

Thanks for any and all help.

Answer 1

等待大神答复