CODEWITHSUNDEEP
sqlpostgresql
Leo
Leo

Reputation: 443

How to re-use two calculated columns

   SELECT 

        day,
        SUM(profit) AS TotalProfit,
        SUM(revenue) AS TotalRevenue,
        TotalProfit/TotalRevenue AS ProfitMargin

    FROM table

It says TotalProfit is not found as a column of table. I want to avoid to do SUM(profit)/ SUM(revenue) because the real formula is much more complicated.

Upvotes: 1

Views: 123

Answers (6)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

My favorite method for doing this is a lateral join. This looks like:

SELECT . . . ,
       v.exp1, v.exp2, v.exp1 / v.exp2
FROM table t, LATERAL
     (VALUES (<expression1>, <expression2>) ) v(exp1, exp2);

However, to use this with an aggregation query, you would need a subquery anyway. And if you are using a subquery, just put the calculation in the outer query.

Upvotes: 0

Cody Thompson
Cody Thompson

Reputation: 11

SELECT *,TotalProfit/TotalRevenue AS ProfitMargin from
 (select
    day,
    SUM(profit) AS TotalProfit,
    SUM(revenue) AS TotalRevenue       
FROM table) x
where

Upvotes: 1

Raymond Nijland
Raymond Nijland

Reputation: 11602

You need to use a subquery. Then you can use data.TotalProfit / data.TotalRevenue AS ProfitMargin

SELECT 
   data.day
 , data.TotalProfit
 , data.TotalRevenue
 , (data.TotalProfit / data.TotalRevenue) AS ProfitMargin
FROM (
  SELECT
      day    
    , SUM(profit) AS TotalProfit
    , SUM(revenue) AS TotalRevenue
  FROM 
   table
)
 data

Upvotes: 1

Vao Tsun
Vao Tsun

Reputation: 51446

in order to use previously declared alias use subquery

SELECT day,TotalProfit, TotalRevenue, TotalProfit/TotalRevenue AS ProfitMargin
from (
SELECT 

        day,
        SUM(profit) AS TotalProfit,
        SUM(revenue) AS TotalRevenue
    FROM table
GROUP BY day
) as subq

Ofcourse I agree with others it looks an overhead

Upvotes: 1

Valli
Valli

Reputation: 1450

You cannot refer Alias name in SElect Clause. You must use group by when using aggregate functions like sum

SELECT 

    day,
    SUM(profit) AS TotalProfit,
    SUM(revenue) AS TotalRevenue,
    SUM(profit)/SUM(revenue) AS ProfitMargin

FROM table

Group By day

Upvotes: 2

nacho
nacho

Reputation: 5397

Do the sum again like this:

 SELECT 
        day,
        SUM(profit) AS TotalProfit,
        SUM(revenue) AS TotalRevenue,
        SUM(profit)/SUM(revenue) AS ProfitMargin

    FROM table
    GROUP BY Day

Upvotes: 1

Related Questions