returning records based on a count statement

[pmsbony]

I am trying to run a query to return adress details of all our clients who have inputted a certain number (< 10) in the last six months.

I am having trouble using the count function in the query can anyone help. The full query as it stands at the moment is pasted below.

select distinct left(address.add_post_code, len(address.add_post_code)-2)
from
address inner join companyaddress on
address.add_id = companyaddress.coa_add_id
inner join company on
companyaddress.coa_cmp_id = company.cmp_id
inner join contact on
company.cmp_id = contact.cntct_cmp_id
inner join instruction on
contact.cntct_id = instruction.ins_cntct_id
where instruction.ins_id in (select count (instruction.ins_id) as 'count'
from instruction where 'count' < '10' and instruction.ins_created > '01-Sep-2001')


any chance someone can assist with my stupidity as I am sure it is something minor.

cheers in advance

peter

 

[JNC73]

Hi,

Your where clause should be something like this

where instruction.ins_id in
(select instruction.ins_id
from instruction
where instruction.ins_created > '01-Sep-2001'
order by client_id
having count(ins_id) > 10)

Client_id is the Id where you want to count the number of records on. I don't know what your fieldnames are so replace
it with your identifier fieldname.


Hope this helps

JNC73