CODEWITHSUNDEEP
mysqlsqldatabase
somethingelse
somethingelse

Reputation: 61

mySQL: how to write a query that satisfies two or more conditions

Given tables: 

Departments: dept_id, dept_name
Employees: dept_id, emp_id, salary

I was trying to come up with a query that shows dept_id, dept_name, avg salary of specific depts, and number of employees in that dept. WHERE dept with less than 20 employees and average dept salary is less than 2000.

Here is the query I came up to display employees with less than 2000 salary:

This is what i came up with for salary

SELECT dept_id, salary
FROM employees
WHERE (salary) < 2000;

And I know this can be used for counting number of people in department 

SELECT count(*) as count,dept.dept_name
FROM employees 
INNER JOIN dept on employees.department_id = dept.department_id 
GROUP BY dept.dept_name;

How would I go about implementing it in a single query so that it meets the specification?

Upvotes: 0

Views: 429

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

Add a HAVING clause to your query, which restricts to only departments having an average salary less than 2000.

SELECT
    d.dept_id,
    d.dept_name,
    COUNT(*) AS count
FROM employees e
INNER JOIN dept d
    ON e.department_id = d.department_id 
GROUP BY
    d.dept_id    -- or maybe GROUP BY d.dept_id, d.dept_name if this doesn't work
HAVING 
    COUNT(*) < 20 AND
    AVG(salary) < 2000;

A WHERE clause applies to individual records, but a HAVING clause applies to groups, after GROUP BY has run. In this case, you want the latter.

Edit: You would be better off grouping by the dept_id column, as this should always be unique (and should be a primary key in this table). The potential risk of grouping by department name is that two different departments might have the same name.

Upvotes: 2

Murat T&#252;fek&#231;i
Murat T&#252;fek&#231;i

Reputation: 93

SELECT count(*) as count,dept.dept_name, salary
FROM employees 
INNER JOIN dept on employees.department_id = dept.department_id 
GROUP BY dept.dept_name HAVING salary < 2000

Upvotes: 1

Related Questions