VFP 9: SQL: count()

How do I, in one query, get the count of the number of distinct
values for a particular column? For example:
clcode other
------ -----
ABC 12
ABC 34
DEF 17
DEF 21
DEF 58
GHI 29

The result that I want is the number of different clcode values in
the above: 3.

I thought I had done this before with but one query, but can not
figure it out now.

Have I been bitten by a bitrot bug or something?

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

Select clcode Group by clcode
?_tally

Dan

Gene Wirchenko wrote:
> How do I, in one query, get the count of the number of distinct
> values for a particular column? For example:
> clcode other
> ------ -----
> ABC 12
> ABC 34
> DEF 17
> DEF 21
> DEF 58
> GHI 29
>
> The result that I want is the number of different clcode values in
> the above: 3.
>
> I thought I had done this before with but one query, but can not
> figure it out now.
>
> Have I been bitten by a bitrot bug or something?
>
> Sincerely,
>
> Gene Wirchenko
>
> Computerese Irregular Verb Conjugation:
> I have preferences.
> You have biases.
> He/She has prejudices.

"Dan Freeman" wrote:

>Select clcode Group by clcode
>?_tally

Blindsided again. I use _tally quite often, and I did not even
think of doing this.

Thank you.

[snip]

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

On Wed, 30 Jul 2008 16:59:17 -0700, Gene Wirchenko wrote:

>"Dan Freeman" wrote:
>
>>Select clcode Group by clcode
>>?_tally
>
> Blindsided again. I use _tally quite often, and I did not even
>think of doing this.
>
> Thank you.

Don't forget to make sure you set _tally to 0 before the select call so you
don't get bitten by the sometimes bug.

>
>[snip]
>
>Sincerely,
>
>Gene Wirchenko
>
>Computerese Irregular Verb Conjugation:
> I have preferences.
> You have biases.
> He/She has prejudices.
My real address is jazzncats at rogers dot com

Hi Gene

> How do I, in one query, get the count of the number of distinct
> values for a particular column? For example:
> clcode other
> ------ -----
> ABC 12
> ABC 34
> DEF 17
> DEF 21
> DEF 58
> GHI 29
>
> The result that I want is the number of different clcode values in
> the above: 3.
>
> I thought I had done this before with but one query, but can not
> figure it out now.

SELECT count(*) FROM (SELECT clcode FROM yourTable GROUP BY clCode)

or

SELECT Count(DISTINCT clcode) FROM yourTable


Regards
Bernhard Sander

Gene Wirchenko wrote:
> "Dan Freeman" wrote:
>
>> Select clcode Group by clcode
>> ?_tally
>
> Blindsided again. I use _tally quite often, and I did not even
> think of doing this.
>
> Thank you.

You're welcome. As Bernhard posts, there are other ways.

One of the great things about VFP is there are always 3 ways to do any given
thing. One of the worst things about VFP is there are always 3 ways to do
any given thing.

Dan

Bernhard Sander wrote:

>> How do I, in one query, get the count of the number of distinct
>> values for a particular column? For example:
>> clcode other
>> ------ -----
>> ABC 12
>> ABC 34
>> DEF 17
>> DEF 21
>> DEF 58
>> GHI 29
>>
>> The result that I want is the number of different clcode values in
>> the above: 3.
>>
>> I thought I had done this before with but one query, but can not
>> figure it out now.
>
>SELECT count(*) FROM (SELECT clcode FROM yourTable GROUP BY clCode)

I tried this one before posting: Syntax error. I thought it
would work, too. Does anyone know what is wrong with it?

>or
>
>SELECT Count(DISTINCT clcode) FROM yourTable

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

>>
>>SELECT count(*) FROM (SELECT clcode FROM yourTable GROUP BY clCode)

> I tried this one before posting: Syntax error. I thought it
> would work, too. Does anyone know what is wrong with it?

You have to add an alias for the derived table:
SELECT count(*) FROM (SELECT clcode FROM yourTable GROUP BY clCode) as somealias
--
JS

i79947-at-fake.uwasa.fi.invalid wrote:

>>>SELECT count(*) FROM (SELECT clcode FROM yourTable GROUP BY clCode)
>
>> I tried this one before posting: Syntax error. I thought it
>> would work, too. Does anyone know what is wrong with it?
>
>You have to add an alias for the derived table:
>SELECT count(*) FROM (SELECT clcode FROM yourTable GROUP BY clCode) as somealias

Thank you. That works.

(It is not very intuitive though. Giving a name that one does
not use?)

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

Hi Gene,

> (It is not very intuitive though. Giving a name that one does
> not use?)
That's true, that's also the reason, why I did not add this silly alias. But I
also did not test that line 8-( . I think, there are a lot of things in this SQL
that I don't find intuitive...
I only tested the other command (... count(DISTINCT *) ... ), since I could not
find this combination in the help file but knew it from other SQL dialects...

Regards
Bernhard Sander