-1

i have a rails app where i am trying to sum values from hstore.

This is the query:

# SELECT *, COALESCE(NULLIF(analytics->'2018.5.17.hits', '')::INT, 0) + COALESCE(NULLIF(analytics->'2018.5.18.hits', '')::INT, 0) + COALESCE(NULLIF(analytics->'20
18.5.19.hits', '')::INT, 0) + COALESCE(NULLIF(analytics->'2018.5.20.hits', '')::INT, 0) + COALESCE(NULLIF(analytics->'2018.5.21.hits', '')::INT, 0) + COALESCE(NULLIF(analyt
ics->'2018.5.22.hits', '')::INT, 0) + COALESCE(NULLIF(analytics->'2018.5.23.hits', '')::INT, 0) + COALESCE(NULLIF(analytics->'2018.5.24.hits', '')::INT, 0) as summed_hits F
ROM "searched_words" WHERE "searched_words"."account_id" = 2 AND (name ILIKE '%%') AND (analytics ?| ARRAY['2018.5.17.hits','2018.5.18.hits','2018.5.19.hits','2018.5.20.hit
s','2018.5.21.hits','2018.5.22.hits','2018.5.23.hits','2018.5.24.hits']) AND (summed_hits > 1) AND (analytics ?| ARRAY['2018.5.17.hits','2018.5.18.hits','2018.5.19.hits','2
018.5.20.hits','2018.5.21.hits','2018.5.22.hits','2018.5.23.hits','2018.5.24.hits']);
ERROR:  column "summed_hits" does not exist
LINE 1: ...22.hits','2018.5.23.hits','2018.5.24.hits']) AND (summed_hit...

I can't seem to grasp, why postgres doesn't recognize summed_hits from the SELECT clouse...

Thanks!

IvRRimUm
  • 1,724
  • 3
  • 21
  • 40

1 Answers1

0

You can't use alias in where, as per:

https://www.postgresql.org/docs/current/static/sql-select.html#SQL-SELECT-LIST

An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132