PostgreSQL error when removing orders placed closer than 2 minutes apart SQL Error [42803]: ERROR: aggregate functions are not allowed in WHERE

Question

I am getting the following error when removing orders placed closer than 2 minutes apart, but I cannot use HAVING as there isn't a GROUP BY in the sub query.

Am I approaching this correctly and should I GROUP something to make this work?

SQL Error [42803]: ERROR: aggregate functions are not allowed in WHERE

SELECT customer_id,
MAX(created_at) last_order_date,
MAX(created_at) + ((SELECT EXTRACT(MINUTE FROM MAX(created_at)-MIN(created_at)) 
FROM (SELECT customer_id, created_at
FROM (SELECT customer_id, created_at, rank() over (partition by customer_id order by created_at desc) lasttwo
FROM orders) sub
WHERE sub.lasttwo <= 2
AND SUM(EXTRACT(MINUTE FROM MAX(created_at)-MIN(created_at))) > 2) s2) ::text||' minute')::INTERVAL AS nextdate,
(SELECT AVG(total_price - total_tax) 
FROM (SELECT customer_id, created_at, total_price, total_tax
FROM (SELECT customer_id, created_at, total_price, total_tax, rank() over (partition by customer_id order by created_at desc) lasttwo
FROM orders) sub
WHERE sub.lasttwo <= 2
AND SUM(EXTRACT(MINUTE FROM MAX(created_at)-MIN(created_at))) > 2) s2) nextvalue 
FROM orders 
GROUP BY customer_id

I would suggest that you delete this question. Then, ask another question with sample data, desired results, and an explanation of what you are trying to do. By the way, the error message seems quite clear. How to fix it is a different matter. — Gordon Linoff, Mar 07 '19 at 20:10
Matt, with your reputation, I would expect you would use CTEs (Common Table Expressions) for this kind of queries. It would be way easier to read and to debug. — The Impaler, Mar 07 '19 at 20:10
Use Having expression for the aggregates.SUM/MAX/MIN can not be used under Where expression. — Oly, Mar 07 '19 at 20:11
Possible duplicate of [How to avoid error "aggregate functions are not allowed in WHERE"](https://stackoverflow.com/questions/20991729/how-to-avoid-error-aggregate-functions-are-not-allowed-in-where) — Henry Woody, Mar 07 '19 at 20:12

The Impaler · Accepted Answer · 2019-03-07T20:35:59.533

OK, I rewrote your query using CTEs, and fixed the aggregation. Here it is:

with
sub as (
  SELECT customer_id, created_at, total_price, total_tax,
    rank() over (partition by customer_id order by created_at desc) lasttwo
  FROM orders
),
s2 as (
  SELECT customer_id, created_at, total_price, total_tax
  FROM sub
  WHERE sub.lasttwo <= 2
  GROUP BY customer_id, created_at, total_price, total_tax -- fix #1
  HAVING SUM(EXTRACT(MINUTE FROM MAX(created_at)-MIN(created_at))) > 2 -- fix #2
),
subx as (
  SELECT customer_id, created_at,
  rank() over (partition by customer_id order by created_at desc) lasttwo
  FROM orders
),
s2x as (
  SELECT customer_id, created_at
  FROM subx
  WHERE sub.lasttwo <= 2
  GROUP BY customer_id, created_at -- fix #3
  HAVING SUM(EXTRACT(MINUTE FROM MAX(created_at)-MIN(created_at))) > 2 -- fix #4
)
SELECT customer_id,
  MAX(created_at) last_order_date,
  MAX(created_at) + ((
    SELECT EXTRACT(MINUTE FROM MAX(created_at)-MIN(created_at)) from s2x
  ) ::text||' minute')::INTERVAL AS nextdate,
  (SELECT AVG(total_price - total_tax) from s2) nextvalue
FROM orders
GROUP BY customer_id

I can't really run this query and test it for real, but you get the idea.

By the way, sub and s2 can be combined into a single CTE. The same can be said about subx and s2x.

PostgreSQL error when removing orders placed closer than 2 minutes apart SQL Error [42803]: ERROR: aggregate functions are not allowed in WHERE

1 Answers1