I don't understand why I cannot use HAVING in this statement

Question

SELECT
  f.*,
  q.day_avg_gmv
FROM
  fact_purchases AS f
  JOIN (
    SELECT
      ds,
      AVG(gmv) AS day_avg_gmv
    FROM
      fact_purchases
    WHERE
      istest = FALSE
    GROUP BY
      ds
  ) AS q 
  ON q.ds = f.ds
WHERE
  f.istest = FALSE
ORDER BY
  f.ds
HAVING
  f.gmv > q.day_avg_gmv

You can see the statement. I've tried to filter the gmv that is more than average. However, I've got this error:

ERROR: syntax error at or near "HAVING"

I know that I can solve the problem using this condition f.gmv > q.day_avg_gmv in WHERE, but I want to know why it's not working in HAVING.

There is no `GROUP BY` in your outermost query. (yes, there is one in the subquery) — wildplasser, May 24 '22 at 11:15
syntactically, having goes before order by. After you fix the syntax error there is logic error. — Salman A, May 24 '22 at 11:25

score 0 · Answer 1 · answered May 24 '22 at 11:18

0

in SQL you can only use having with group by.

you can not use it without group by.

answered May 24 '22 at 11:18

Emad

82
3

2

This is not correct. See also https://stackoverflow.com/questions/6924896/having-without-group-by – Jonas Metzler May 24 '22 at 11:24
we use `where` for filtering rows and `having` for filtering groups. there is no meaning when we do not use `group by` to use `having`. in your case it is better to put `having` filtering in `where` – Emad May 24 '22 at 19:26

I don't understand why I cannot use HAVING in this statement

1 Answers1