Reputation: 352

Using SUM() result in query

I have one little problem. I am using a query like this:

SELECT SUM(...) AS result1, SUM(...) AS result2 FROM ...

and my question is how to use result1 and result2 in expression, something like this:

SELECT SUM(...) AS result1, SUM(...) AS result2, result1*100/result2 AS percentage FROM ...

Is there a way just use results of SUM() function or other?

I've tried just like this above, but this returns an error.

Upvotes: 1

Answers (3)

Reputation: 842

It is easiest to just use the SUM() again,

so it would be:

SELECT 
    SUM(var1) AS result1, 
    SUM(var2) AS result2, 
    (SUM(var1)*100/SUM(var2)) AS percentage 
FROM ...

MySQL/SQL doesn't thus far allow you to use the custom columns you are making, they have to be defined columns in the database.

Upvotes: 1

Reputation: 1271151

You can use a subquery or you can repeat the expressions:

SELECT SUM(...) AS result1,
       SUM(...) AS result2, 
       SUM(...)*100/SUM(...) AS percentage
FROM ...

SQL doesn't allow you to refer to column aliases at the same level of a query.

Upvotes: 1

Reputation: 64496

There 2 ways either you need to repeat the whole expression for sum or either use subselect

SELECT SUM(first_col) AS result1, SUM(second_col) AS result2 
SUM(first_col) * 100 /SUM(second_col) percentage 
FROM ...

Or a subselect

SELECT result1, result2 , result1*100/result2 AS percentage 
FROM (
SELECT SUM(first_col) AS result1, SUM(second_col) AS result2 
FROM ...
) t

Upvotes: 2