MySQL Database calculate total sum


#1

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.


#2

I don’t believe it’s possible to select both the individual row values AND the total in a single query, but summing a bunch of values together in javascript is pretty performant, so I don’t know that I’d be worried about trying to optimize that away.

Your logic seems a bit flawed to me here too… you probably don’t want the sum of cost and quantity as there’d be no way to know just from the sum which items have which cost, and which have what quantity.

Anyway, the raw query that would replicate what you have with lucid would be:

result = await Database.raw(`SELECT
                               sum(cost),
                               sum(quantity),
                               sum((cost * quantity)) total
                             FROM invoice_items
                             WHERE invoice_id = ?
                             GROUP BY invoice_id`, [11])

FWIW it’s usually best practice to use integers for monetary value storage, and then divide by 100 for display to move the decimal point to the proper position, avoids rounding errors when dealing with taxes and so forth. :wink:


#3

Thanks for your answer.
So what do you think would be the best way to get the total values?

Calculate them in the Model with getters or setters for example? Or storing the total net value directly in the database table while saving the model?


#4

is not a good idea store a calculated column on your existing database, if you need to show the a * b result; then you can calculate it on your controller and then send data to the view


#5

Okay - So this is what i made now in my Model for the invoice items:


  static get computed() {
    return ['totalNet', 'totalGross', 'totalVat'];
  }

  getTotalNet({ cost, quantity }) {
    return cost * quantity;
  }

  getTotalGross({ cost, quantity }) {
    const totalNet = cost * quantity;
    const totalGross = totalNet * 1.19;
    return totalGross;
  }

  getTotalVat({ cost, quantity }) {
    const totalNet = cost * quantity;
    const totalVat = totalNet * 0.19;
    return totalVat;
  }

My returned Object looks now like this:

"invoiceItems": [
            {
                "id": 9,
                "invoice_id": 11,
                "key": 0,
                "item": "",
                "description": "Description Item 1",
                "cost": 50,
                "quantity": 40,
                "totalNet": 2000,
                "totalGross": 2380,
                "totalVat": 380
            },
         ........

I’m using Computed properties in my Model to calculate the Net, Gross and Vat from the item. So the total values are never saved in the database and will be calculated “on the fly” in the models.

The VAT will come from another database table, it’s currently hardcoded for testing.

Is this a good approach? :slight_smile:


#6

Without knowing any other information, looks totally fine, yep.


#7

So this is now my final code with which i ended up:

  async store({ request }) {
    /*
     * Get all invoice items from the invoice
     */
    let invoiceItems = request.input('invoiceItems');

    /*
     * Loop through the invoice items to get the total amount of the invoice
     */
    let invoiceNet = 0;
    await _.forEach(invoiceItems, function(value) {
      invoiceNet += value.cost * value.quantity;
    });

    /*
     * Get the latest invoice number from the last record
     */
    const lastInvoice = await Database.select('invoice_number')
      .from('invoices')
      .orderBy('invoice_number', 'desc')
      .first();

    /*
     * Save the new invoice
     */
    const invoice = await Invoice.create({
      invoice_number: lastInvoice.invoice_number + 1,
      invoice_net: invoiceNet,
      contact_id: request.input('contactId'),
      invoice_date: request.input('invoiceDate'),
      due_date: request.input('dueDate')
    });

    /*
     * Save the related invoice items
     */
    await invoice.invoiceItems().createMany(invoiceItems);

    return invoice;
  }

I’m now saving the total net sum of to the invoice table. To get the total amount i’m getting all invoice items from the request, loop through the object to calculate the final total amount which is then saved to the actual invoice.

Is this code still fine, or could i improve and clean up the code? :):slight_smile: