CODEWITHSUNDEEP
sqlpostgresqlgroup-byaggregate-functionswhere-clause
Slazer
Slazer

Reputation: 4990

Aggregate function not accepted inside WHERE clause

I have the following command.

SELECT
 supplier_name, EXTRACT(DOW FROM received) AS day_of_week, SUM(quantity) AS total_quantity
FROM 
 supplier NATURAL JOIN store
WHERE
 item_id = (SELECT item_id FROM item WHERE item_name='Samsung Galaxy A50 A505F')
 AND
 received >= TIMESTAMP '2019-03-01 00:00:00 Europe/Prague'
 AND 
 received <  TIMESTAMP '2019-04-01 00:00:00 Europe/Prague'
 AND
 supplier_name='Amazon'
 AND
 day_of_week=3 -- this is the problem
GROUP BY
 supplier_name, day_of_week;

With this PostgreSQL schema.

item(item_id, item_name)
supplier(supplier_id, supplier_name)
store(id, item_id, supplier_id, price, vat, quantity, received)

After adding AND day_of_week=3 to WHERE I get the following error.

pgdb->     GROUP BY
pgdb->      supplier_name, day_of_week;
ERROR:  column "day_of_week" does not exist
LINE 14:      day_of_week=3

Why cannot I restrict this column?

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You cannot use a derived column in a where clause. A simple solution is a lateral join:

SELECT . . ., v.day_of_week, . . .
FROM supplier su JOIN
     store st
     ON ??? CROSS JOIN LATERAL
     (VALUES (EXTRACT(DOW FROM ?.received))) v(day_of_week)
WHERE . . . AND
      v.day_of_week = 3
GROUP BY . . .

Some advice:

  • Do not use NATURAL JOIN. There is nothing "natural" about it, because it does not recognize properly declared foreign key relationships. And it is very highly prone to error. And it makes the code unreadable to others (and often your future self) because the JOIN criteria are not explicitly stated.
  • Use table aliases.
  • Qualify all column references in the query.

Upvotes: 1

Related Questions