James
James

Reputation: 299

Figure out which employee has done more training

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

Answers (1)

Gouri
Gouri

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

Related Questions