Cfrgtkky

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

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;

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