unique values combined with COUNT()

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • preek
    New Member
    • Aug 2008
    • 4

    #1

    unique values combined with COUNT()

    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
    Last edited by preek; Aug 28 '08, 04:19 PM. Reason: insufficient title
  • code green
    Recognized Expert Top Contributor
    • Mar 2007
    • 1726

    #2
    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.

    Comment

    • preek
      New Member
      • Aug 2008
      • 4

      #3
      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

      Comment

      • code green
        Recognized Expert Top Contributor
        • Mar 2007
        • 1726

        #4
        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

        Comment

        Working...