SQL - How to reference outerquery new column in subquery-CodePudding

I'd like to create a new variable in an outerquery, and then manipulate this new variable in a subquery.

However, when referencing the new column in the subquery, I receive an error that no such column exists

Code:

dbGetQuery(db, 
  "SELECT id, var1/var2 AS new_var, (SELECT SUM(new_var) AS summed_var
    FROM table 
    GROUP BY id) 
  FROM table")

In this above code, the issue is that new_var doesn't get passed to the subquery. How can I pass the newly defined column, new_var to the subquery?

EDIT: switching the inner query/outer query still doesn't work:

dbGetQuery(db, 
  "SELECT SUM(new_var) AS summed_var (SELECT id, var1/var2 AS new_var
    FROM table)
  FROM table
  GROUP BY id")

EDIT 2: The data is structured as below. Each row is an observation for an id. I would like to generate a ratio, new_var = var1/var2 for each line. Then, I would like to sum the ratio new_var for each id. In the toy data, for example, the summed ratio for Sally would be 3/4 7/8.

My challenge was using GROUP BY with SQL. I was unsure how to perform GROUP BY id after generating the new_var on a line-by-line basis.

id       var1     var2    new_var
sally     3        4         3/4
susan     5        6         5/6
sally     7        8         7/8
tom       9        10        9/10

CodePudding user response：

Assuming that you actually want to show the result but not the 'equation 3/4' the following should do what you want:

select 
    id
  , sum(var1/var2) as summed_var 
from Tble 
group by id

All expressions other than involving aggregation results (i.e. those standing alone, or inside aggregation functions like SUM, COUNT, etc) are evaluated for each row( which means that var1/var2 is calculated for each row), then the aggregation (SUM) is performed for each group formed by Id (GROUP BY Id)

Is this what you seek?

CodePudding user response：

Use SUM() window function:

SELECT *,
       ROUND(1.0 * var1 / var2, 2) new_var,
       ROUND(SUM(1.0 * var1 / var2) OVER (PARTITION BY id), 2) summed
FROM tablename;

or:

SELECT *,
       ROUND(1.0 * var1 / var2, 2) new_var,
       ROUND(1.0 * SUM(var1) OVER (PARTITION BY id) / SUM(var2) OVER (PARTITION BY id), 2) summed
FROM tablename;

or if you want the results as text:

SELECT *,
       var1 || '/' || var2 new_var,
       SUM(var1) OVER (PARTITION BY id) || '/' || SUM(var2) OVER (PARTITION BY id) summed
FROM tablename;

See the demo.