I got a transaction file where one of the fields is the date and another is the
client code. I want to count how many clients I have per day.

Carlos,

> I got a transaction file where one of the fields is the date and another is the
> client code. I want to count how many clients I have per day.

I don't think DBISAM supports "SELECT COUNT(DISTINCT Column) FROM Table"
yet so you will probably need to use something like:

SELECT DISTINCT CLIENT_ID FROM ATable WHERE DateField = ADate

....and then read Query.RecordCount to get the 'clients per day' count.

Eryk

Is there a way to make something like this?

SELECT T.DATE, DISTINCT T.CLIENTnum FROM Table T

I want all the transaction with date eliminating the duplicates clients



CARLOS



Eryk Bottomley <no@way.com> wrote:

Carlos,

> I got a transaction file where one of the fields is the date and another is the
> client code. I want to count how many clients I have per day.

I don't think DBISAM supports "SELECT COUNT(DISTINCT Column) FROM Table"
yet so you will probably need to use something like:

SELECT DISTINCT CLIENT_ID FROM ATable WHERE DateField = ADate

....and then read Query.RecordCount to get the 'clients per day' count.

Eryk

I think, this can only be done with temp table.

SELECT DISTINCT T.DATE, T.CLIENTnum
INTO Memory\TempTable
FROM Table T;

CREATE UNIQUE INDEX ON Memory\TempTable(DATE, CLIENTnum);

SELECT T.Date, Count(T.CLIENTnum) from Memory\TempTable group by T.Date;

Later on you will have to drop the TempTable:
DROP Memory\TempTable;

You can try to use subselect, but the above method will work faster.

Regards,
Michael


Carlos wrote:
> Is there a way to make something like this?
>
> SELECT T.DATE, DISTINCT T.CLIENTnum FROM Table T
>
> I want all the transaction with date eliminating the duplicates clients
>
>
>
> CARLOS
>
>  
>
> Eryk Bottomley <no@way.com> wrote:
>
> Carlos,
>
>> I got a transaction file where one of the fields is the date and another is the
>> client code. I want to count how many clients I have per day.
>
> I don't think DBISAM supports "SELECT COUNT(DISTINCT Column) FROM Table"
> yet so you will probably need to use something like:
>
> SELECT DISTINCT CLIENT_ID FROM ATable WHERE DateField = ADate
>
> ....and then read Query.RecordCount to get the 'clients per day' count.
>
> Eryk
>