Hi Nick,
ta muchly - that's done it. I had to alias the subselect and
change count(propertyID > 1) to count(propertyID) > 1 to end up
with this as the final solution:
SELECT COUNT(Multiples),client_id
FROM (SELECT COUNT(PropertyID) Multiples,
Client_ID
from inspection
GROUP BY Client_ID, PropertyID
HAVING COUNT(PropertyID) > 1) as temp
group by client_id
cheers everyone!
Toby
Thursday, September 18, 2003, 2:08:16 PM, you wrote:
NG> If your looking for the number of times clients visited a property more than
NG> once
NG> Try this (I think it's syntactically correct but it should at least be a
NG> good pointer)
NG> SELECT COUNT(Multiples)
NG> CLIENT_ID
NG> FROM (SELECT COUNT(Property_ID) Multiples,
NG> Client_ID
NG> GROUP BY Client_ID, Property_ID
NG> HAVING COUNT(Property_ID > 1)
NG> GROUP BY Client_ID
NG> -----Original Message-----
NG> From: [EMAIL PROTECTED]
NG> [mailto:[EMAIL PROTECTED] On Behalf Of Toby Tremayne
NG> Sent: Thursday, 18 September 2003 1:29 PM
NG> To: CFAussie Mailing List
NG> Subject: [cfaussie] OT: sql question
NG> Hi All,
NG> I have a mildly tricky one for you - the english explanation of
NG> what I want to do is as follows:
NG> when a client inspects a property, a record is created. What I
NG> need returned, is a count per client, of how many times they've
NG> inspected properties more than once. For instance:
NG> the client has inspected property A twice, property B three times,
NG> and property C twice. The count of multiple inspections should
NG> come back equalling 3.
NG> this make sense to anyone? I'm positive I've seen or perhaps even
NG> done something like this before, but I can't for the life of me
NG> remember how to achieve it. Any help would be greatly appreciated
NG> ;)
NG> cheers,
NG> Toby
NG> --------------------------------
NG> Life is Poetry,
NG> write it in your own words
NG> --------------------------------
NG> Toby Tremayne
NG> Cold Fusion Developer
NG> Code Poet and Zen Master of the Heavy Sleep
NG> Virtual Tours
NG> +61 416 048 090
NG> ICQ: 13107913
NG> ---
NG> You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To
NG> unsubscribe send a blank email to [EMAIL PROTECTED]
NG> MX Downunder AsiaPac DevCon - http://mxdu.com/
NG> ---
NG> You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
NG> To unsubscribe send a blank email to [EMAIL PROTECTED]
NG> MX Downunder AsiaPac DevCon - http://mxdu.com/
--------------------------------
Life is Poetry,
write it in your own words
--------------------------------
Toby Tremayne
Cold Fusion Developer
Code Poet and Zen Master of the Heavy Sleep
Virtual Tours
+61 416 048 090
ICQ: 13107913
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MX Downunder AsiaPac DevCon - http://mxdu.com/
