CODEWITHSUNDEEP
mysqlsqldatabase
Nicasso
Nicasso

Reputation: 265

Sql query with an OR operator between a HAVING and WHERE clause

Would it be possible to combine a having and a where clause in an OR operator within a single SQL query?

Maybe not the best example, but you'll get the idea:

Select the departments from the employee table that is HR (using the where clause) OR that pays all employees more than 25000 (using the having clause). So how do we get the OR condition in the query down below? Or would it be better to separate the query into 2 queries.

SELECT dept, SUM (salary) 
FROM employee 
WHERE dept = "HR"
GROUP BY dept 
HAVING SUM (salary) > 25000

Upvotes: 0

Views: 467

Answers (3)

JamieA
JamieA

Reputation: 2013

The below will work - you do not have to specify an aggregate in the HAVING clause

SELECT dept, SUM (salary) 
FROM employee 
GROUP BY dept 
HAVING dept = "HR" or SUM (salary) > 25000

But your statement "that pays all employees more than 25000 " is not clear. Do you want

Departments where all employees earn over 25000 each, or departments where all employees earn over 25000 in total?

The query above gives you the second option, as that is closest to your original query

Upvotes: 4

P.Salmon
P.Salmon

Reputation: 17615

Maybe you want dept where all salaries are over 25000.

drop table if exists employees;
create table employees(id int auto_increment primary key, dept varchar(2), salary int);

insert into employees (dept,salary)
values
('HR',10000),('aa',10000),('aa',45000),('bb',25000),('cc',26000),('cc',26000);

select dept,sum(salary) sumsalary,count(*) obs, sum(case when salary > 25000 then 1 else 0 end) over25000
from employees
group by dept having obs = over25000 or dept = 'hr'

+------+-----------+-----+-----------+
| dept | sumsalary | obs | over25000 |
+------+-----------+-----+-----------+
| cc   |     52000 |   2 |         2 |
| HR   |     10000 |   1 |         0 |
+------+-----------+-----+-----------+
2 rows in set (0.01 sec)

Upvotes: 1

jarlh
jarlh

Reputation: 44766

Wrap the GROUP BY part up in a derived table. Then apply the conditions to its result:

select dept, salarysum
from
(
    SELECT dept, SUM (salary) as salarysum
    FROM employee 
    GROUP BY dept
) dt
where salarysum > 25000 or dept = "HR"

Or, perhaps, "that pays all employees more than 25000", means that no dept employee earns less than 25000?

select dept, minsalary
from
(
    SELECT dept, MIN(salary) as minsalary
    FROM employee 
    GROUP BY dept
) dt
where minsalary > 25000 or dept = "HR"

Upvotes: 2

Related Questions