Postgres: Select min/max... over the result of another select-CodePudding

I have a select query like this:

SELECT name, SUM(table1.tons_of_data * (table2.tons_of_data * (1/1000)) AS value
FROM table1
INNER JOIN table2 ON table1.name = table2.name
WHERE table1.tons_of_data IS NOT NULL
AND table2.tons_of_data IS NOT NULL
GROUP BY value

Resulting in something like:

| name | value |
________________
   a.      1
   b.      2
   c.      3

With more than 1M rows as results

Then I need to calculate min, max, percentiles OVER the value column output. I tried of something like:

SELECT name, SUM(table1.tons_of_data * (table2.tons_of_data * (1/1000)) AS value,
MIN(table1.tons_of_data * (table2.tons_of_data * (1/1000)) as min
...
FROM table1
INNER JOIN table2 ON table1.name = table2.name
WHERE table1.tons_of_data IS NOT NULL
AND table2.tons_of_data IS NOT NULL
GROUP BY value

Wrongly resulting in

 | name | value |. min | max |
 ______________________________
    a.      1       1.     1
    b.      2.      2.     2
    c.      3.      3.     3

In a fantasy world, what I would like to have is:

| name | value |
________________
   a.      1
   b.      2
   c.      3

And then, somehow the result of the MIN, MAX, etc over the value column like this

| min | max |
________________
   1      3

Is there any way to achieve what I need without persisting the result of the first query and avoiding repeating the maths within the query? (also avoiding the min/max results over each cell, which is totally useless)

Thanks a lot in advance

CodePudding user response：

This is a typical case for using a cte :

With list AS
(
SELECT name, SUM(table1.tons_of_data * (table2.tons_of_data * (1/1000)) AS value
FROM table1
INNER JOIN table2 ON table1.name = table2.name
WHERE table1.tons_of_data IS NOT NULL
AND table2.tons_of_data IS NOT NULL
GROUP BY name
)
SELECT name, value, min(value) over w AS min, max(value) over w AS max
  FROM list
WINDOW w AS ()