How to calculate two column values on select query using MYSQL?

Question

how to calculate two columns values in select query using mysql?

Here is my sql query

SELECT cusName, 
       remarks, 
       invoiceNo,
       invoiceDate, 
       total_VAT, 
       bill_tot,
       ROUND(((bill_tot - total_VAT) * 5/100),2) as vatamt,
       ROUND(bill_tot - total_VAT, 2) as exclvat                   
FROM invoices 
where invoiceDate between '2018-11-13' and '2018-11-13'
order by invoiceID;

In the above query i need to calculate sum value of vatamt and exclvat as total amount and display it.Can anyone help me please to do this?

Madhur Bhaiya · Accepted Answer · 2018-11-15 09:00:42Z

1

You cannot reuse an aliased Calculated expression again inside a SELECT clause. They can be reused only in either GROUP BY, ORDER BY, HAVING clauses. You will need to specify the calculation expressions again, to calculate the total_amount:

SELECT cusName, 
       remarks, 
       invoiceNo,
       invoiceDate, 
       total_VAT, 
       bill_tot,
       ROUND((bill_tot - total_VAT) * 5/100,2) as vatamt, -- got rid of extra parentheses
       ROUND(bill_tot - total_VAT, 2) as exclvat,
       ROUND((bill_tot - total_VAT) * 5/100,2) + 
       ROUND(bill_tot - total_VAT, 2) as total_amount
FROM invoices 
where invoiceDate between '2018-11-13' and '2018-11-13'
order by invoiceID;

answered Nov 15, 2018 at 9:00

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

blackwhite Over a year ago

is it possible to calculate all the sum of total_amount?

Madhur Bhaiya Over a year ago

@blackwhite yes. just put this complete query under a FROM clause, and use SUM() over it. This is called derived tables. Refer this documentation for more understanding: dev.mysql.com/doc/refman/8.0/en/derived-tables.html

blackwhite Over a year ago

SELECT cusName, remarks, invoiceNo, invoiceDate, ROUND((bill_tot - total_VAT) * 5/100,2) as vatamt, ROUND(bill_tot - total_VAT, 2) as exclvat, ROUND((bill_tot - total_VAT) * 5/100,2) + ROUND(bill_tot - total_VAT, 2) as total_amount FROM (SELECT SUM(total_amount) AS sum_total_amount FROM invoices where invoiceDate between '2018-11-13' and '2018-11-13' order by invoiceID;

Madhur Bhaiya Over a year ago

@blackwhite no. you are doing it other way around. your internal subquery should be outside instead.

blackwhite Over a year ago

i need to get total sum of three columns ie;vatamt,exclvat and total_amount

|

Tim Biegeleisen · Accepted Answer · 2018-11-15 09:07:52Z

0

The answer given by @Madhur is probably the most performant. But in MySQL 8 we could make use of a CTE to avoid repeating common logic in the select clause:

WITH cte AS (
    SELECT *,
        ROUND(((bill_tot - total_VAT) * 5/100),2) AS vatamt,
        ROUND(bill_tot - total_VAT, 2) AS exclvat
    FROM invoices 
    WHERE invoiceDate = '2018-11-13'
)

SELECT
   cusName, 
   remarks, 
   invoiceNo,
   invoiceDate, 
   total_VAT, 
   bill_tot,
   vatamt,
   exclvat,
   vatamt + exclvat AS total
FROM cte
ORDER BY
    invoiceID;

answered Nov 15, 2018 at 9:07

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

3 Comments

Madhur Bhaiya Over a year ago

Are CTEs materialized in MySQL ? I have never used them yet. How are they different in performance terms, compared to Derived Tables.

Tim Biegeleisen Over a year ago

@MadhurBhaiya I believe CTEs are in fact materialized in MySQL, but they are only materialized once (contrast this with a view, which may be materialized multiple times, depending on the query).

Madhur Bhaiya Over a year ago

Ok. version 5.7 and onwards MySQL optimized Derived tables and Views: dev.mysql.com/doc/refman/5.7/en/derived-table-optimization.html I believe that same should be applicable for CTEs.

Dan Soap · Accepted Answer · 2018-11-15 09:09:16Z

0

If you wanted to rank readability over performance (although the performance impact might be negligible) you could also use a wrapping query:

SELECT cusName, 
       remarks, 
       invoiceNo,
       invoiceDate, 
       total_VAT, 
       bill_tot,
       vatamt,
       exclvat,
       vatampt + exclvat as total_amount
FROM (
    SELECT *,
           ROUND(((bill_tot - total_VAT) * 5/100),2) as vatamt,
           ROUND(bill_tot - total_VAT, 2) as exclvat                   
    FROM invoices 
    WHERE invoiceDate between '2018-11-13' and '2018-11-13'
    ORDER BY invoiceID
) a;

edited Nov 15, 2018 at 9:09

answered Nov 15, 2018 at 9:05

Dan Soap

10.3k2 gold badges43 silver badges50 bronze badges

3 Comments

Madhur Bhaiya Over a year ago

Just a note: MySQL version 5.7 and above only optimize the Derived tables. Previous versions materialize the subquery, which reduces performance.

blackwhite Over a year ago

@MadhurBhaiya am using MySQL Server 5.5.So shall i go with ur code?

Madhur Bhaiya Over a year ago

@blackwhite yes in that case, my query would be most performant in terms of memory usage.

Collectives™ on Stack Overflow

How to calculate two column values on select query using MYSQL?

3 Answers 3

12 Comments

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

12 Comments

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related