CODEWITHSUNDEEP
sqlpostgresql
atlasofcoffee
atlasofcoffee

Reputation: 1

Same return with and without the SUM operator PostgreSQL

I'm using PostgreSQL 10 and trying to run this query. I started with a CTE which I am referencing as 'query.'

SELECT
  ROW_NUMBER()OVER() AS my_new_id,
    query.geom AS geom,
    query.pop AS pop,
    query.name,
    query.distance AS dist,
    query.amenity_size,
    ((amenity_size)/(distance)^2) AS attract_score,
    SUM((amenity_size)/(distance)^2) AS tot_attract_score,
    ((amenity_size)/(distance)^2) / SUM((amenity_size)/(distance)^2)  as marketshare
INTO table_mktshare
FROM query
WHERE
    distance > 0
GROUP BY
    query.name,
    query.amenity_size,
    query.geom,
    query.pop,
    query.distance

The query runs but the problem lies in the 'markeshare' column. It returns the same answer with or without the SUM operator and returns one, which appears to make both the attract_score and the tot_attract_score the same. Why is the SUM operator read the same as the expression above it?

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

This is occurring specifically because each combination of columns in the group by clause uniquely identifies one row in the table. I don't know if this is intentional, but more normally, one would expect something like this:

SELECT ROW_NUMBER() OVER() AS my_new_id,
       query.geom AS geom,  query.pop AS pop, query.name,
       SUM((amenity_size)/(distance)^2) AS tot_attract_score,
INTO table_mktshare
FROM query
WHERE distance > 0
GROUP BY query.name, query.geom, query.pop;

This is not your intention, but it does give a flavor of what's expected.

Upvotes: 1

Related Questions