CODEWITHSUNDEEP
sqlsql-server
Sin5k4
Sin5k4

Reputation: 1626

Using calculated fields over and over again with a new table

I'm fairly new to SQL and i had to do some calculations using a table.Imagine we have a table with fields : ID - Name - Val1 - Val2 ; Lets say i want to add up 2 values and add it to my query result.I can do that easily with a sub query such as:

select val1+val2 as valtotal,* from my table.

Now if i want to do some more process on valtotal, i use a derived table such as;

select valtotal*3 as ValMoreCalculated,* from
(select val1+val2 as valtotal,* from my table) AS A

A bit more code maybe??

select ValMoreCalculated/valtotal as ValEvenMoreCalc ,* from 
(select valtotal*3 as ValMoreCalculated,* from
    (select val1+val2 as valtotal,* from my table) AS A)AS B

So if i want to do more calculations with the ValMoreCalculated do i have to go through another derived table? Name it as B for example? Is there an easier way to achieve this in SQL?

PS:the title is a bit off i know,but couldn't figure out what to name it :P

Upvotes: 0

Views: 121

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Your sample can be simplified to not use calculations but if you really need to reuse some complicated calculation in more than one place you can make use of cross apply. It is still sub-querying but it just looks a bit different. 

select T3.ValMoreCalculated/T2.Valtotal as ValEvenMoreCalc, *
from YourTable as T1
  cross apply (select T1.Val1 + T1.Val2) as T2(ValTotal)
  cross apply (select T2.ValTotal * 3) as T3(ValMoreCalculated)

Upvotes: 1

Jeremy
Jeremy

Reputation: 9020

You should be able to perform the calculation in one fell swoop instead of subquerying.

Example:

select (val1+val2)*3 as ValMoreCalculated,* from my table

Edit

I felt it necessary to address your comment. If you want to conditionally multiple by 3, for example, you could do it like this:

select (val1+val2)*CASE WHEN <some expression> = 'SomeValue' THEN 3 ELSE 1 END as ValMoreCalculated,* from my table

Working example:

Declare @val1 int 
Declare @val2 int 
Declare @someExpression int 
Set @val1 = 1 
Set @val2 = 2 
Set @someExpression = 1

select (@val1+@val2)*CASE WHEN @someExpression = 1 THEN 3 ELSE 1 END as ValMoreCalculated

Also, to address your question edit:

select ((val1+val2)*3)/(val1+val2) as ValEvenMoreCalc,* from my table

The key is if your calculation is 1-to-1 with a record, it is not required to subquery. If, for example, you want any grouping, then you may. If subquerying makes it more readable to you, then it's simply a matter of preference. For clarity, I tend to use comments. You can't get much clearer than that:

--Calculates x based on y to get a gross profit %
select ((val1+val2)*3)/(val1+val2) as ValEvenMoreCalc,* from my table

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269553

The answer to your question is that you need the subqueries, if you want all the intermediate results.

Most dialects of SQL do not let you use a column alias within the same select statement where it is defined.

You can start to create complicated expressions. However, I find subqueries to be much clearer and more maintainable than unnecessarily complicated statements.

I want to add two things. First, there is no performance hit with using subqueries in this case. If that is your issue, the data is only read once.

Second, you might be interested in computed columns on tables. This is a SQL Server feature that allows you to define a new column, derived from other columns in the table. So, valTotal could be stored within the table, as an expression, without taking up any data space.

Upvotes: 0

Related Questions