CODEWITHSUNDEEP
sqlsql-server
bramvs
bramvs

Reputation: 39

using aggregate function in other aggregate function in the SELECT statement SQL

I am having this pretty big aggregate function that I have to use twice in another column, is there a way to write this down without having to write out the first function three times? So I would like something like this:

Select
volumet =
CASE When sum(volume)>200, then 200
     Else sum(volume)
END,
sum(volumet * price) / volumet - price2 * volumet as Profit
FROM tablex

Upvotes: 0

Views: 49

Answers (2)

Sean Lange
Sean Lange

Reputation: 33571

Something like this should be what you are looking for based on your original post.

WITH CTE 
AS
(
    SELECT
        sum(volumet) AS volumet
    ,   price
    ,   price2
    FROM tablex
    group by price, price2
)
, VolumetValues as
(
    select case when volumet > 200 then 200 else volumet end as volumet
        , price
        , price2
    from CTE
)

SELECT 
    (volumet * price) / volumet - price2 * volumet as Profit
FROM VolumetValues

Upvotes: 0

João Mendes
João Mendes

Reputation: 1425

Select
sum(volumet * price) / volumet - price2 * volumet as Profit
FROM
(Select
CASE When sum(volume)>200, then 200
     Else sum(volume)
END as volumet, price, price2
FROM tablex) as tableAux

Upvotes: 1

Related Questions