CODEWITHSUNDEEP
mysql
GusDeCooL
GusDeCooL

Reputation: 5758

Do math in MySQL from SELECT

I have MySQL query like this

SELECT `transaction`.id,
CONCAT(contact.`name`, " ", contact.last_name) as fullName,
(SELECT SUM(total) FROM transaction_product WHERE transaction_product.ref_transaction = `transaction`.id) as subtotal,
    (SELECT SUM(transaction_payment.idr_cash + transaction_payment.idr_bni + transaction_payment.idr_ame_exp + transaction_payment.idr_cc_niaga) FROM transaction_payment WHERE transaction_payment.`transaction` = `transaction`.id ) AS payment,
    (subtotal - payment) as balance
FROM `transaction`
LEFT JOIN contact
    ON contact.id = `transaction`.contact_id
WHERE reservation_status = 3
    AND `transaction`.id = 6345

As you see, I want to get the balance which that math come in SELECT. (subtotal - payment) as balance
how should I do that?

Upvotes: 4

Views: 31798

Answers (2)

Mark Byers
Mark Byers

Reputation: 838106

You can't use aliases that are defined in the SELECT clause to calculate other columns that are also in the same SELECT clause. You have at least three options:

  1. Repeat the subquery every time you need to use it. This is has the disavantage that you will need to repeat a lot of code. Since your subqueries are long and complex, this is an undesirable option.

  2. Use a subquery and an outer query.

    SELECT
    *,
    (subtotal - payment) AS balance
FROM
(
     SELECT 
         ...,
         (...) AS subtotal,
         (...) AS payment
     FROM ... 
) T1

  3. Use a JOIN instead of subselects. This is slightly more complex for your situation, but it will be better for performance if you ever need to fetch more than one row.

Upvotes: 11

dash
dash

Reputation: 91482

The simplest way is probably to use your initial result as a subquery, hence:

SELECT          id,
                fullName,
                subtotal,
                payment,
                (subtotal-payment) as balance

FROM                     
(
  SELECT `transaction`.id, 
      CONCAT(contact.`name`, " ", contact.last_name) as fullName, 
      (SELECT SUM(total) FROM transaction_product WHERE transaction_product.ref_transaction = `transaction`.id) as subtotal, 
      (SELECT SUM(transaction_payment.idr_cash + transaction_payment.idr_bni + transaction_payment.idr_ame_exp + transaction_payment.idr_cc_niaga) FROM transaction_payment WHERE transaction_payment.`transaction` = `transaction`.id ) AS payment
  FROM `transaction` 
  LEFT JOIN contact 
      ON contact.id = `transaction`.contact_id 
  WHERE reservation_status = 3 
      AND `transaction`.id = 6345
  )

Upvotes: 6

Related Questions