Finding Number of Records

[Cynthia_Blue]

I'm attempting to query a database to find out how many occurances of a particular value exist.

So... I'm trying to select where the same VendorNumber occurs less than twice, or more than twice. If it only occurs twice I do not care. I would like to also be able to see what the Value of the VendorNumber is.

I've tried various syntax with count, distinct, and having, but I'm not sure I understand how it all works and so I've had no luck.

In brief, I have a table called Vendor, which has the following entries for the VendorNumber field:

123123
123456
123456
123789
123789
123789

I'd like the query to tell me there is 1 occurance of less than 2, and there is 1 occurance of more than 2. It would be fine if these are separated out into two different queries to put into a php page. I'd also like to see the VendorNumber displayed.

I would appreciate any help! Thanks in advance.

[devinemke]

SELECT COUNT(*) AS repetitions, VendorNumber FROM Vendor GROUP BY VendorNumber HAVING repetitions < 2

[Cynthia_Blue]

Thanks.. I forgot to mention it's a Sybase database. And I do know sybase does not support LIMIT, perhaps it doesn't support 'repititions' either, as I got this error:

sybase_query(): Sybase: Server message: Invalid column name 'repetitions'.

[Lars_Berg]

SELECT COUNT(*) AS repetitions, VendorNumber 
FROM Vendor 
 GROUP BY VendorNumber 
HAVING count(*) <> 2

[Cynthia_Blue]

Works beautifully thanks! 🙂