CODEWITHSUNDEEP
sqlpostgresql
reptilicus
reptilicus

Reputation: 10417

Aggregation/SUM SQL

Suppose I have a table in postgres like such:

id, name, area, value_1, value_2
1, Bob, 123.4, 45, 50
2, Bob, 45.1, 30, 30
3, Ross, 234, 40, 40

I'm trying to do something like this:

select SUM(area) as total_area, 
name, 
SUM(value_1) as sum_value_1, 
SUM((1.0 - (value_1 / 100.0 * value_2 / 100.0) * (area / total_area))) as normalized_value
from my_tbl 
group by name

This clearly doesn't work, as total_area is not defined. I'm guessing some sort of nested select might work? Any ideas?

Upvotes: 0

Views: 46

Answers (1)

fthiella
fthiella

Reputation: 49089

If I understand your logic correctly, you can use some a window function to calculate the total area along all other unaggregated columns in a subquery, and then aggregate by name (and total_area):

SELECT
  name,
  total_area,
  SUM(value_1) AS sum_value_1,
  SUM((1.0 - (value_1 / 100.0 * value_2 / 100.0) * (area / total_area))) as normalized_value
FROM (
  SELECT
    name,
    area,
    SUM(area) OVER (partition BY name) as total_area,
    value_1,
    value_2
  FROM
    my_tbl
) s
GROUP BY
  name, total_area

Please see a fiddle here.

Upvotes: 1

Related Questions