How to count the number of unique values in a column?

[Eugene Lutsenko]

How to quickly count the number of unique values in a database column?

Code: Select all

INDEX ON FIELDGET(ff) TO Mrk_funi UNIQUE

[Auge_Ohr]

How to quickly count the number of unique values in a database column?

Code: Select all

INDEX ON FIELDGET(ff) TO Mrk_funi UNIQUE

try this

Code: Select all

*- the following translation is for column total summaries
#xtranslate TOTAL(<field>,<cond>) => ;
{ || TOTALFOOTER( { || < field > }, { || < cond > } ) }


/*------------------------------------------------------------------------
 TOTALFOOTER()

 Total the column and return result as a string.

 SYNTAX:  TotalFooter(expB1, expB2, expC1)

    <expB1> -  code block containing column expression to total
    <expB2> -  code block while condition
    <expC1> -  filter expression

 RETURNS:  String value of total
 --------------------------------------------------------------------------*/
FUNCTION TOTALFOOTER( bExpr, bFor )
LOCAL nSaveRec
LOCAL nTotal   := 0

   nSaveRec := RECNO()
   DBGOTOP()
   DBEVAL( { || nTotal := nTotal + EVAL( bExpr ) }, { || EVAL( bFor ) },,,, .T. )
   DBGOTO( nSaveRec )

RETURN LTRIM( STR( nTotal ) )

[Eugene Lutsenko]

COUNT TO

[Auge_Ohr]

COUNT TO

so i misunderstood what you want to do...

[Eugene Lutsenko]

COUNT TO

so i misunderstood what you want to do...

Excuse me. This is due to the fact that I use machine translation into English

[Tom]

Be aware of that a UNIQUE indexed record that is being removed does NOT automatically cause the maybe following record with the same data to be inside the index! If you have an unique index on "NAME" on a table like this:

NAME NO
Miller 1
Harper 2
Miller 3

"Miller 1" will be in the unique index. If you delete "Miller 1", "Miller 3" will NOT be in the index - you will find nothing when searching "Miller" then. This will only happen if you a) create the index again or b) replace the name of "Miller 3" with "Miller".