CODEWITHSUNDEEP
mysql
blackwhite
blackwhite

Reputation: 37

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

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?

Upvotes: 1

Views: 2018

Answers (3)

Dan Soap
Dan Soap

Reputation: 10248

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;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

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;

Upvotes: 0

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

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;

Upvotes: 1

Related Questions