Reputation: 299
I have 2 tables, employees and classes_taken. I am trying to see which employees has taken more than 2 class. However, I am getting this error ERROR: aggregate functions are not allowed in WHERE Position: 87
.
CREATE TABLE employees (
id integer primary key,
name text
);
CREATE TABLE classes_taken (
employee integer,
class text,
foreign key (employee) references employees(id)
);
INSERT INTO employees (id, name) VALUES
(1, 'bob'), (2, 'sam'), (3, 'mike');
INSERT INTO classes_taken (employee, class) VALUES
(1, 'swimming'), (1, 'dancing'), (2, 'swimming'), (2, 'tennis'), (3, 'golf'), (3, 'dancing');
My select statement.
select e.id, e.name
FROM employees e
JOIN classes_taken c
ON e.id = c.employee
WHERE count(c.class) > 2
GROUP BY c.class;
SQLFiddle: http://sqlfiddle.com/#!15/5296cb/4
Upvotes: 0
Views: 30
Reputation: 347
You need to keep the count filter in a HAVING clause, because it is on an aggregated value.
select e.id, e.name
FROM employees e
INNER JOIN classes_taken c ON e.id = c.employee
GROUP BY e.id, e.name
HAVING count(c.class) > 2
Upvotes: 1