CODEWITHSUNDEEP
sql
clovercentral
clovercentral

Reputation: 23

Is there any way that I can do further calculations using another calculated column alias name in SQL?

My Original:

SELECT 
    100 AS Price, 0.07 AS TaxRate, 
    100 * 0.07 AS TaxAmount, 
    100 + (100 * 0.07) AS Total

Can I write it this way? How do I do that?

SELECT 
    100 AS Price, 0.07 AS TaxRate, 
    Price * TaxRate AS TaxAmount, 
    Price + TaxAmount AS Total.

OR another example...

My original:

SELECT 
    ProductName, ListPrice, DiscountPercent,
    ListPrice * (DiscountPercent/100) AS DiscountAmount, 
    ListPrice - (ListPrice * (DiscountPercent/100)) AS DiscountPrice
FROM
    Products
ORDER BY 
    DiscountPrice DESC

Desired:

SELECT 
    ProductName, ListPrice, DiscountPercent, 
    ListPrice * (DiscountPercent/100) AS DiscountAmount, 
    ListPrice - DiscountAmount AS Discount Price
FROM
    Products
ORDER BY 
    DiscountPrice DESC

Upvotes: 1

Views: 48

Answers (1)

O. Jones
O. Jones

Reputation: 108686

A common table expression (CTE) can do this.

WITH Detail AS (
  SELECT 100 AS Price, 0.07 AS TaxRate
)
SELECT Price, TaxRate, 
       Price * TaxRate AS TaxAmount, 
       Price * (1.0 + TaxRate) AS Total
  FROM Detail

If your database doesn't do CTEs, use a subquery.

SELECT Price, TaxRate, 
       Price * TaxRate AS TaxAmount, 
       Price * (1.0 + TaxRate) AS Total
  FROM (
      SELECT 100 AS Price, 0.07 AS TaxRate
  ) Detail

Each level of query can be thought of as a virtual table, with columns that you can name as necessary. So you can use the names of a subquery (or CTE) in an outer query.

Upvotes: 1

Related Questions