CODEWITHSUNDEEP
sqlpostgresql
Antonio
Antonio

Reputation: 134

How to multiply column by value gotten from previous query?

I calculated the slope and intercept for two columns in the table:

select regr_slope(x, y) slope, regr_intercept(x, y) intercept
from table

Now I want to multiply the columns x with slope and add intercept, like so:

select x, y, slope * x + intercept as y_fit 
from table

How can I achieve this in one query?

Upvotes: 0

Views: 76

Answers (2)

wangd
wangd

Reputation: 21

I'd probably use a subquery and join it.

select x, y, (subq.slope * x + subq.intercept) y_fit 
from table,
     (select regr_slope(x, y) as slope, regr_intercept(x,y) as intercept 
     from table) subq;

Note that subq is necessary because subqueries need aliases, in general.

I'm assuming that regr_slope and regr_intercept are aggregation functions, otherwise why wouldn't you just inline and do select x, y, regr_slope(x,y) * x + regr_intercept(x,y) from table.

You're basically doing a full cartesian join of your original table with the one-row result of your aggregation.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270411

You can use window functions:

select x, y,
      regr_slope(x, y) over () * x + regr_intercept(x, y) over () as y_fit 
from table

Upvotes: 1

Related Questions