How to fix error “aggregate functions are not allowed in WHERE”

Question

How to fi this error

Err] ERROR: aggregate functions are not allowed in WHERE

this my query

select count(case daftar.daftar when 'sd' then 1 else null end) as sd,
 count(case daftar.daftar when 'smp' then 1 else null end) as smp,
 count(case daftar.daftar when 'sma' then 1 else null end) as sma
from daftar
join gelombang on  daftar.gel=gelombang.id
join ajaran on ajaran.id=gelombang.id_ajar
join tahun on tahun.id=ajaran.tahun
where daftar.status='terima' and daftar.pindahan='no' and tahun.id= max(tahun.id)

This problem already discussed here: http://stackoverflow.com/questions/21466123/error-aggregates-not-allowed-in-where-clause-in-postgresql — Maksym Voronytskyi, Dec 22 '16 at 06:47
BTW: You can simplify the `CASE`expressions, e.g. `COUNT(daftar.daftar = 'sd' OR NULL)`. — clemens, Dec 22 '16 at 06:49
Possible duplicate of [ERROR: Aggregates not allowed in WHERE clause in postgreSQL](http://stackoverflow.com/questions/21466123/error-aggregates-not-allowed-in-where-clause-in-postgresql) — Aziz Shaikh, Jan 20 '17 at 10:00

score 7 · Answer 1 · answered Apr 13 '17 at 16:09

You can use "HAVING" to tackle this:

HAVING tahun.id= max(tahun.id)

select count(case daftar.daftar when 'sd' then 1 else null end) as sd,
 count(case daftar.daftar when 'smp' then 1 else null end) as smp,
 count(case daftar.daftar when 'sma' then 1 else null end) as sma
from daftar
join gelombang on  daftar.gel=gelombang.id
join ajaran on ajaran.id=gelombang.id_ajar
join tahun on tahun.id=ajaran.tahun
where daftar.status='terima' and daftar.pindahan='no' 
HAVING tahun.id= max(tahun.id)

score 3 · Accepted Answer · answered Dec 22 '16 at 06:47

One option is to use a subquery to calculate that max value:

select count(case daftar.daftar when 'sd' then 1 else null end) as sd,
       count(case daftar.daftar when 'smp' then 1 else null end) as smp,
       count(case daftar.daftar when 'sma' then 1 else null end) as sma
from daftar
inner join gelombang
    on daftar.gel = gelombang.id
inner join ajaran
    on ajaran.id = gelombang.id_ajar
inner join tahun
    on tahun.id = ajaran.tahun
where daftar.status = 'terima' and
      daftar.pindahan = 'no'   and
      tahun.id = (select max(id) from tahun)

score 3 · Answer 3 · answered Dec 22 '16 at 06:51

3

Aggregates functions we use only in SELECT block. You can use inner select for this case:where daftar.status='terima' and daftar.pindahan='no' and tahun.id=(select max(id) from tahun)

answered Dec 22 '16 at 06:51

Mr. Skip

417
4
10

This is not completely correct. You may use aggregates in `HAVING` clauses, too. – clemens Dec 22 '16 at 06:53
thank for your kindness and advice I'm trying to learn aggregates – faza Dec 22 '16 at 07:06

score 1 · Answer 4 · answered Dec 22 '16 at 07:09

1

use a subquery, group by or having clause

answered Dec 22 '16 at 07:09

kimdasuncion12

319
2
4
17

How to fix error “aggregate functions are not allowed in WHERE”

4 Answers4

Linked