Menu
What I am trying to do is add another column that calculates (cr - dr)
Seeing as you cannot re-use an alias inside a SELECT clause, how would you go about calculatin total
SELECT SUM(b.bet_win * cy.fx_rate )as dr, SUM(b.bet_loss * cy.fx_rate ) as cr, cr+dr as total
FROM ....
WHERE ....
425
No there isn't a way to refer to aliases, but you can assign the expression to a variable, and then refer to the variable in the same select clause. Inside a select statement variable assignment is always done by the infix operator := . *In a SET statement, it can be either = or := .
In PROC SQL, a column alias can be used in a WHERE clause, ON clause, GROUP BY clause, HAVING clause, or ORDER BY clause. In the ANSI SQL standard and ISO SQL standard, the value that is associated with a column alias does not need to be available until the ORDER BY clause is executed.
The following SQL statement creates three aliases, alias ID for STUDENT_ID column, alias NAME for STUDENT_NAME column and alias ADDRESS for STUDENT_ADDRESS column.
An SQL alias is useful for simplifying your queries and making the query and its result more readable. This article explains why and how you can use aliases in your SQL queries. You can temporarily rename a table or a column by giving it another name. This is known as an SQL alias.
In SQL Server or Oracle, I'd use a CTE, but since you're using MySQL, you'd use a subquery:
SELECT dr, cr, cr + dr as total
FROM (
SELECT
SUM(b.bet_win * cy.fx_rate ) as dr,
SUM(b.bet_loss * cy.fx_rate ) as cr
FROM ....
WHERE ....) t;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
