Same return with and without the SUM operator PostgreSQL

Question

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?

Probably because you're grouping by amenity_size.

Jacob H
– Jacob H

2018-02-22 19:54:03 +00:00
Commented Feb 22, 2018 at 19:54 — Jacob H
– Jacob H, Commented Feb 22, 2018 at 19:54

Gordon Linoff · Accepted Answer · 2018-02-22 20:05:15Z

1

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.

answered Feb 22, 2018 at 20:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Same return with and without the SUM operator PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related