By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

unique values combined with COUNT()

P: 4
Hi there,

The following statement produces multiple results, but I only need one.

SELECT CREDITOR_NAME, CREDITOR_NO, INV_ID, TRAIN_COUNT, COUNT(CREDITOR_NO) as number_of_inv
FROM DIPPMX.ADMIN.TI$ST_RECO
GROUP BY CREDITOR_NAME, INV_ID, TRAIN_COUNT, CREDITOR_NO
HAVING COUNT(CREDITOR_NO) >= 5

Normally I would include the CREDITOR_NAME in MIN(), but this wont work due to the COUNT(CREDITOR_NO). Any help is appreciated.

Thanks in advance,
Alain
Aug 28 '08 #1
Share this Question
Share on Google+
3 Replies


code green
Expert 100+
P: 1,726
You probably need a WHERE condition to filter your result.
You haven't explained which one result you need and why, but you could use TOP.
Aug 28 '08 #2

P: 4
I have found my solution; I actually did use MIN() as suggested:

SELECT CREDITOR_NAME, CREDITOR_NO, MIN(INV_ID), COUNT(CREDITOR_NO) as no_of_inv
FROM DIPPMX.ADMIN.TI$ST_RECO
GROUP BY CREDITOR_NAME, CREDITOR_NO
HAVING COUNT(CREDITOR_NO) >= 3
Aug 28 '08 #3

code green
Expert 100+
P: 1,726
OK. Well done.
So you needed the lowest INV_ID number. This is generally the oldest.
I have to admit I cannot see what you are trying to do.
It helps future posters who may have a similar problem if you explain why you need such a specific result
Aug 29 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.