CODEWITHSUNDEEP
sqloracle-database
letstryit
letstryit

Reputation: 3

Oracle SQL select group function as where clause

I have a simple SQL which result is basic information of employees.

select emp_name, emp_firstname, emp_location, emp_salary from employees e
where e.emp_location = 'XYZ'

Now I ONLY want to get a result of that above mentioned SQL if the sum of ALL employees salary of the location is over 1.000.000 EUR. Otherwise the result should be NULL.

I created a select statement which does analyzes the sum of all employees and returns NULL or the SUM value over 1.000.000 EUR:

select sum(emp_salary) from employees e
where e.emp_location = 'XYZ'
having sum(emp_salary) > 1000000

When I now try to combine both SQL:

select emp_name, emp_firstname, emp_location, emp_salary from employees e
where e.emp_location = 'XYZ'
having sum(emp_salary) > 1000000

I get the error ORA-00937 not a single-group group function

Upvotes: 0

Views: 127

Answers (3)

d r
d r

Reputation: 7891

Maybe something like here (10000 limit):

Select EMPNO, ENAME, SAL, LOC, DEPTNO, CASE WHEN DEPT_SAL > 10000 THEN DEPT_SAL END "DEPT_SAL" 
From (  Select      emp.EMPNO, emp.ENAME, emp.SAL, dept.LOC, dept.DEPTNO, Sum(emp.SAL) OVER(Partition By dept.DEPTNO) "DEPT_SAL" 
        From        emp 
        Inner Join  dept ON(dept.DEPTNO = emp.DEPTNO)) 
WHERE LOC = 'NEW YORK'

     EMPNO ENAME             SAL LOC               DEPTNO   DEPT_SAL
---------- ---------- ---------- ------------- ---------- ----------
      7939 HILLER           1300 NEW YORK              10      13850 
      7934 MILLER           1300 NEW YORK              10      13850 
      7369 SMITH             800 NEW YORK              10      13850 
      7902 FORD             3000 NEW YORK              10      13850 
      7839 KING             5000 NEW YORK              10      13850 
      7782 CLARK            2450 NEW YORK              10      13850

-- without where condition
Select EMPNO, ENAME, SAL, LOC, DEPTNO, CASE WHEN DEPT_SAL > 10000 THEN DEPT_SAL END "DEPT_SAL" 
From (  Select      emp.EMPNO, emp.ENAME, emp.SAL, dept.LOC, dept.DEPTNO, Sum(emp.SAL) OVER(Partition By dept.DEPTNO) "DEPT_SAL" 
        From        emp 
        Inner Join  dept ON(dept.DEPTNO = emp.DEPTNO)) 

     EMPNO ENAME             SAL LOC               DEPTNO   DEPT_SAL
---------- ---------- ---------- ------------- ---------- ----------
      7939 HILLER           1300 NEW YORK              10      13850 
      7934 MILLER           1300 NEW YORK              10      13850 
      7369 SMITH             800 NEW YORK              10      13850 
      7902 FORD             3000 NEW YORK              10      13850 
      7839 KING             5000 NEW YORK              10      13850 
      7782 CLARK            2450 NEW YORK              10      13850 
      7788 SCOTT            3000 DALLAS                20            
      7566 JONES            2975 DALLAS                20            
      7876 ADAMS            1100 DALLAS                20            
      7654 MARTIN           1250 CHICAGO               30            
      7521 WARD             1250 CHICAGO               30            
      7499 ALLEN            1600 CHICAGO               30            
      7900 JAMES             950 CHICAGO               30            
      7698 BLAKE            2850 CHICAGO               30            
      7844 TURNER           1500 CHICAGO               30

... without where condition and without CASE expression:

Select EMPNO, ENAME, SAL, LOC, DEPTNO, DEPT_SAL
From (  Select      emp.EMPNO, emp.ENAME, emp.SAL, dept.LOC, dept.DEPTNO, Sum(emp.SAL) OVER(Partition By dept.DEPTNO) "DEPT_SAL" 
        From        emp 
        Inner Join  dept ON(dept.DEPTNO = emp.DEPTNO)) 

     EMPNO ENAME             SAL LOC               DEPTNO   DEPT_SAL
---------- ---------- ---------- ------------- ---------- ----------
      7939 HILLER           1300 NEW YORK              10      13850 
      7934 MILLER           1300 NEW YORK              10      13850 
      7369 SMITH             800 NEW YORK              10      13850 
      7902 FORD             3000 NEW YORK              10      13850 
      7839 KING             5000 NEW YORK              10      13850 
      7782 CLARK            2450 NEW YORK              10      13850 
      7788 SCOTT            3000 DALLAS                20       7075 
      7566 JONES            2975 DALLAS                20       7075 
      7876 ADAMS            1100 DALLAS                20       7075 
      7654 MARTIN           1250 CHICAGO               30       9400 
      7521 WARD             1250 CHICAGO               30       9400 
      7499 ALLEN            1600 CHICAGO               30       9400 
      7900 JAMES             950 CHICAGO               30       9400 
      7698 BLAKE            2850 CHICAGO               30       9400 
      7844 TURNER           1500 CHICAGO               30       9400

Upvotes: 0

Bogdan Dincescu
Bogdan Dincescu

Reputation: 728

Or, if you wish to have sum(emp_salary) by location>1000000, then:

with tb_sal_loc as (
select emp_name, emp_firstname, emp_location, emp_salary 
  ,sum(emp_salary) over (partition by emp_location) loc_salaries
from employees e
where e.emp_location = 'XYZ')
select *
from b_sal_loc
where loc_salaries>1000000;

After your last reply, have you tried the above? This is supposed to do exactly what you have specified in your last reply.

Upvotes: 0

lemon
lemon

Reputation: 15502

You select non-aggregated fields, but you use no GROUP BY clause. By fixing it, your query should work properly:

SELECT emp_name, emp_firstname, emp_location, SUM(emp_salary) AS emp_salary 
FROM employees
WHERE emp_location = 'XYZ' 
GROUP BY emp_name, emp_firstname, emp_location
HAVING SUM(emp_salary) > 1000000

Upvotes: 1

Related Questions