<div>
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.<br />

</div>


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.


<div>
suggest 30 to me and 40 to izblank
</div>


<div>
<div class="content wysiwyg-content">
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. &nbsp;I can find the result by a simple query as below, but I am asked not to use simple query to do this. &nbsp;ie. replacing the Group by or Having clause with subquery. &nbsp;Actually, I don't know how to do so. &nbsp;Can anyone help?<br />
<br />
select staff_no, office_no, count(job_id)<br />
from staff<br />
group by staff_no, office_no<br />
HAVING count(job_id) &gt;= 5<br />
;
</div>
</div>


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
;

Replace Group by and Having Clause with composite query

FileMaker Pro is a cross-platform relational database application that integrates a database engine with a graphical user interface (GUI) and security features, allowing users to modify the database by dragging new elements into layouts, screens, or forms. FileMaker evolved from a DOS application, but since 1992 has been available for Microsoft Windows and Mac OS, and can be used in a cross-platform environment.