PostgreSQL WHERE cause to filter out results with 0 causes a syntax error-CodePudding

I'm trying to produce a SQL query where I have my IDs on the first column, then sum of page hits of accounts in column 2 and page hits of payments in column 3. The below code works, except for one point. I want to exclude any rows which have 0 in accounts column. When I add in the WHERE clause

SELECT
  EVAR3 AS MYID
  sum(case when web.Page.name = 'Accounts:Overview' then 1 else 0 end) as accounts
  sum(case when web.Page.name = 'Payment:Overview' then 1 else 0 end) as payments
FROM mytable
WHERE
timestamp >= to_timestamp('2021-09-01') AND timestamp <= to_timestamp('2021-09-02')
AND
(sum(case when web.Page.name = 'Accounts:Overview' then 1 else 0 end) > 0)
GROUP BY EVAR3
ORDER BY EVAR3 ASC

This gives me an invalid expression error on my where clause saying generate expressions are not valid in the where clause. When I change WHERE to HAVING I get a syntax error saying it expected EOF rather than GROUP.

How do I correctly implement a filter to remove results with 0 in accounts to otherwise working code?

CodePudding user response：

Since it is an aggregate function it should be HAVING but it should appear after the GROUP BY. the structure is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

SELECT
  EVAR3 AS MYID,
  sum(case when web.Page.name = 'Accounts:Overview' then 1 else 0 end) as accounts,
  sum(case when web.Page.name = 'Payment:Overview' then 1 else 0 end) as payments
FROM mytable
WHERE
timestamp >= to_timestamp('2021-09-01') AND timestamp <= to_timestamp('2021-09-02')

GROUP BY EVAR3
HAVING (sum(case when web.Page.name = 'Accounts:Overview' then 1 else 0 end) > 0)
ORDER BY EVAR3 ASC