• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

Replace Group by and Having Clause with composite query

Hi, I was asked to do a stupid job that need to find the staff no and office no of a staff that has 5 or more job on hand.  I can find the result by a simple query as below, but I am asked not to use simple query to do this.  ie. replacing the Group by or Having clause with subquery.  Actually, I don't know how to do so.  Can anyone help?

select staff_no, office_no, count(job_id)
from staff
group by staff_no, office_no
HAVING count(job_id) >= 5
;
0
vanvancela
Asked:
vanvancela
  • 2
2 Solutions
 
jdlambert1Commented:
You have to get a count by staff and office, and group by/having is the correct way to do it. Anything else will cause unnecessarily worse performance.
0
 
NickUpsonSenior Network EngineerCommented:
your question says you don't need the actual number of jobs on hand

create table tmp1 as SELECT s2.job_id, count(*) as num FROM staff s2

select s1.staff_no, s1.office_no
from staff s1, tmp1 t
WHERE s1.job_id = t.job_id AND num > 5

0
 
izblankCommented:

select distinct staff_no, office_no
from staff s1
where (select count(distinct job_id) from staff s2
            where s2.staff_no=s1.staff_no
            ) >5
0
 
NickUpsonSenior Network EngineerCommented:
suggest 30 to me and 40 to izblank
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now