What is the command for a count distinct?

[Bryndis]

What is the command for a SQL style count distinct using a specified column?

 

[DSummZZZ]

I don't know the single command but this works:

SELECT DISTINCT SomeColumn FROM SomeTable ;
INTO CURSOR SomeCursor
?_TALLY

USE IN SomeCursor


Dave S.

 

[Bryndis]

Thanks for the suggestion! I just have 1 more question - what do you mean by SomeCursor? As you may have guessed I am pretty new to VFP.

TIA, again!
Bryn

 

[DSummZZZ]

Same as SomeColumn and SomeTable. Shorthand for whatever your applicable names are. At the risk of stating the obvious (but then you said you were new to VFP), SomeCursor will be in this case a cursor, which is defined as CURrent Set Of Records. I.e., a subset of records in a table.

Dave S.

 

[danceman]

Have you looked at the COUNT command, this should give you what you want.

COUNT Command
Counts table records.

Syntax

COUNT
[Scope] [FOR lExpression1] [WHILE lExpression2]
[TO VarName]
[NOOPTIMIZE]

Arguments

Scope

Specifies a range of records to be included in the count. The scope clauses are: ALL, NEXT nRecords, RECORD nRecordNumber, and REST. For more information on scope clauses, see the Scope Clauses online topic. Commands that include Scope operate only on the table in the active work area.

The default scope for COUNT is ALL records.

Attitude is Everything

 

[baltman]

If you've already run a "select distinct", reccount() is good way to get the count.

Brian

 

[Bryndis]

I did look at the COUNT command but what I need is to get VFP to count for each variation within a column.
e.g. how many SA? how many NI? how many RL? etc

something like:
count distinct column

currently I use:
set filt to column = 'SA'
count

which gives me what I want but not too elegant and if I want to know how many different values are in this column I need to set filt to each value and then add them up at the end and hope it matches

hence, my dilemma.

TIA
Bryn

 

[Mike Gagnon]

Bryndis

Try grouping your records.

 

[baltman]

Try:

Select Distinct ColumnName,Sum(1) as Count;
group by ColumnName from DataTable;
into table ResultsTable

Brian

 

[DSummZZZ]

Here's my variation:

SELECT SomeField, COUNT(SomeField) AS howmany ;
FROM SomeTable GROUP BY SomeField ;
INTO CURSOR SomeCursor

Dave S.