Menu
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)
557
Aggregate functions are not allowed because the WHERE clause is used for filtering data before aggregation. So while WHERE isn't for aggregation, it has other uses. To filter data based on an aggregate function result, you must use the HAVING clause.
1 dept_name, ID, avg (salary) FROM instructor GROUP BY dept_name; This statement IS erroneous because a) Avg(salary) should not be selected b) Dept_id should not be used in group by clause c) Misplaced group by clause d) Group by clause is not valid in this query Answer: b Explanation: Any attribute that is not present ...
Explanation: We apply the aggregate function to a group of sets of tuples using the group by clause. The group by clause must always be used whenever we are willing to apply the aggregate function to a group of sets of tuples.
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)
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)
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)
3
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
