CODEWITHSUNDEEP
sqlsql-server
Atchoum
Atchoum

Reputation: 733

Using the results of an aggregate function in a calculation

I need to use the result of an aggregate function in a calculation. Let's say I need to use a MAX function and add it to another field.

One way of doing it I found was to use a derived table:

SELECT DerivedTable.MaxValue, TableA.Col2 + DerivedTable.MaxValue FROM (
SELECT ID, MAX(Col1) AS MaxValue FROM TableA
) As DerivedTable 
INNER JOIN TableA ON DerivedTable.ID=TableA.ID

Is there any way to do it in a single Select statement?

TIA.

Upvotes: 0

Views: 100

Answers (2)

Kyle Hale
Kyle Hale

Reputation: 8120

This uses a subselect instead of an outright derived table.

select TableA.Col2 + (select max(col1) from tablea t2 where t2.id = tablea.id group by t2.id)
    from TableA

Upvotes: 0

Lamak
Lamak

Reputation: 70658

In SQL Server 2005+ you can use OVER():

SELECT MAX(Col1) OVER(PARTITION BY ID) MaxValue, 
       Col2 + MAX(Col1) OVER(PARTITION BY ID)
FROM TableA

Upvotes: 2

Related Questions