Question 169737 in Mysql Question posted in
The official MySQL documentation can be found here.

How do I calculate two aliases’ values in MySQL?

How do I calculate these two aliases and show them in other columns?

SELECT SUM(qty1) AS ali_qty1,
    (SELECT SUM(qty2) FROM table2 WHERE col2 = 'B') AS ali_qty2,
    (SELECT SUM(ali_qty1) - SUM(ali_qty2) as total_qty)
FROM table1
WHERE col1= 'A'

table1

ID | col1  | qty1
------------------
1  | A       | 50
2  | A       | 20

table2

ID | col2  | qty2
------------------
1  | B       | 50
2  | B       | 10

expected output

ID | ali_qty1  | ali_qty2 | ali_qty3
-------------------------------------
1  |  70          | 60        | 10
Tags:

2

Answers


  1. 0

    For this data, we could just cross join two aggregate queries, then perform the computation:

    select t1.ali_qty1, t2.ali_qty2, t1.ali_qty1 - t2.ali_qty2 ali_qty3
from (select sum(qty1) ali_qty1 from table1 where col1 = 'A') t1
cross join (select sum(qty2) ali_qty2  from table2) t2

    Note that this always returns one row – however any of the two sums may be null, in which case you might want to use coalesce() in the subtraction.

    Login or Signup to reply.
  2. 0 
    SELECT ali_qty1, ali_qty2 , (ali_qty2 - ali_qty1) AS ali_qty3
FROM (
select
(select sum(qty1)   from table1 where col1='A')  as ali_qty1,
(select sum(qty2)   from table2 where col2='B') as ali_qty2
) T1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search