SELECT query

[cf coder]

Hello there,

I was hoping someone could help here. I have a table with two columns, one called 'person_name' and the other called 'number'. A person can have more than one number and I want the query to return all person's that have more than 3 numbers.

Jamie 1
Jamie 2
Jamie 3
Jamie 4
Allan 5
Vicki 6
Marilyn 7
Paul 8
Scott 9
Scott 10
Scott 11
Scott 12

So in the above example, the query would return Jamie and Scott. How do I get this data?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3143
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Peter Boughton]

> I was hoping someone could help here. I have a table with two columns,
> one called 'person_name' and the other called 'number'. A person can
> have more than one number and I want the query to return all person's
> that have more than 3 numbers.

Try this:

SELECT person_name
FROM table
GROUP BY person_name
HAVING COUNT(number) > 3

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3144
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[cf coder]

I'm really grateful to you for replying to my post.

>> I was hoping someone could help here. I have a table with two columns,
>> one called 'person_name' and the other called 'number'. A person can
>> have more than one number and I want the query to return all person's
>> that have more than 3 numbers.
>
>Try this:
>
>SELECT person_name
>FROM table
>GROUP BY person_name
>HAVING COUNT(number) > 3

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3145
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6