I’ve a database table which contains several of items. Each item has a quantity and a column of cost.
Now i want to get all items which are related to the unique
invoice_id get the total sum of each table row and then calculate the total cost. Is this possible with one single SQL statement? And is this the best way to calculate the total cost of an invoice?
So to calculate the total cost “on the fly” instead of storing the total sum to the database? Or should i store the total net cost of each invoice ( or maybe each invoice item? ) in the database?
I attached an image of my table structure:
Further this is my current lucid query
const sum = await Database.select('*') .from('invoice_items') .sum('cost', 'quantity') .where('invoice_id', 11) .groupBy('invoice_id');
But how would i write the SQL statement with knex?
SELECT cost, quantity, cost * quantity as 'total' .....
So i want to select each table with the same
invoice_id - multiply quantity*cost for each row and then get the total sum of all multiplied results.