Oracle error: ORA-00979: not a GROUP BY expression

Question

Following is the result of my first query (in Oracle). Totals is the total number of employees in each department (so Employee 1-5 are on department#1, employees 6-8 on department#2 etc.)

select a.name, (select count(*) from employee b where a.dname = b.dname group by b.dname) as totals
from employee a;

NAME                          TOTALS
------------------------------ ----------------
Employee 1                                    5
Employee 2                                    5
Employee 3                                    5
Employee 4                                    5
Employee 5                                    5
Employee 6                                    3
Employee 7                                    3
Employee 8                                    3
Employee 9                                    4
Employee 10                                   4
Employee 11                                   4
Employee 12                                   4

And here is my second query on which employer has less taskhours than 5 (note "taskhours" is a different field than "totals" in the database)

select a.name
from job o, employee a, works w , task t
where w.name=a.name and w.menuid=o.menuid and w.worktype='office' and t.fname = o.fname
group by a.name
having sum(t.taskhours) <5 
;

NAME
------------------------------
Employee 3

I want now combine the above, i.e query for the employees who have fewer task-hours than the total employees in the department. I am trying this query

select a.name
from job o, employee a, works w , task t
where w.name=a.name and w.menuid=o.menuid and w.worktype='office' and t.fname = o.fname
group by a.name
having sum(t.taskhours) <(select count(*) from employee b where a.sname = b.sname group by b.sname)

but I get the error:

ERROR at line 5:
ORA-00979: not a GROUP BY expression

Can someone help me find the right query?

Sample data for 1st query:

NAME                          DNAME
------------------------------ ------------------------------
Employee 1                            Dep1
Employee 2                            Dep1
Employee 3                            Dep1
Employee 4                            Dep1
Employee 5                            Dep1
Employee 6                            Dep2
Employee 7                            Dep2
Employee 8                            Dep2
Employee 9                            Dep3
Employee 10                           Dep3
Employee 11                           Dep3
Employee 12                           Dep3

Answer 1

We can phrase this by putting each of your two queries into subqueries, and then joining them together:

SELECT
    e.name,
    e.dname,
    t1.emp_cnt,
    t2.hours_cnt
FROM employee e
LEFT JOIN
(
    SELECT dname, COUNT(*) AS emp_cnt
    FROM employee
    GROUP BY dname
) t1
    ON e.dname = t1.dname
LEFT JOIN
(
    SELECT a.name, SUM(t.taskhours) AS hours_cnt
    FROM works w
    INNER JOIN employee a
        ON w.name = a.name
    INNER JOIN job o
        ON w.menuid = o.menuid
    INNER JOIN task t
        ON t.fname = o.fname
    WHERE w.worktype = 'office'
    GROUP BY a.name
) t2
    ON e.name = t2.name
WHERE
    t1.emp_cnt < t2.hours_cnt;

Answer 2

You can simplify the first query as follows

use that as a cte block

with tot_count
  as (select dept_id,count(emp_id) as dept_cnt
        from employee
     group by dept_id)
   ,less_than_five
    as (select a.name
               ,sum(t.taskhours) as sum_taskhours
               ,max(a.dept_id) as dept_id_of_emp
          from job o, 
               employee a, 
               works w , 
               task t
        where w.name=a.name 
         and w.menuid=o.menuid 
         and w.worktype='office' 
         and t.fname = o.fname
       group by a.name 
        )
   select *
     from less_than_five a
     join tot_count b
       on a.dept_id_of_emp=b.dept_id
    where less_than_five.sum_taskhours<=b.dept_cnt