Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 417
  • 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
 
Nick UpsonPrincipal Operations 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
 
Nick UpsonPrincipal Operations EngineerCommented:
suggest 30 to me and 40 to izblank
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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