count field groups

[ameedoo3000]

hi all
how can i count the groups in any filed of the table and put the results in a variable ?
in count command " count to (variable) "
i want that but counting the groups in on filed.
i looking for SQL statment "SELECT COUNT(Distinct Field_name)from table_name".but i can not control putting the results in a variable.

 

[Tore Bleken]

[pre]Select ... Into Array laResult
Wait Window laResult[/pre]
If the array is only one element, as it will be in this case, you can address it directly.

 

[Mike Lewis]

Well, if there are multiple groups, you obviously can't put the result in a single variable. The closest you can get to that is to put them into an array.

Try this:

LOCAL ARRAY laCounts
SELECT GroupID, COUNT(*) FROM TheTable GROUP BY GroupID ;
  INTO ARRAY laCounts

You will end up with a 2-column array. The first column will contain the group ID, the second will be the count for that group.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[ameedoo3000]

This code is suitable for Forms. What about the reports?
What is the code for this situation in the reports?

 

[Olaf Doschke]

Mike, that gives the count of records for each group. COUNT(Distinct field) will just give one value.

We talk about the difference of

Select type, Count(*) from foxcode group by type

Select Count(distinct type) from foxcode

The first one gives 9 result rows with a count per type, the second gives 9, the number of distinct types.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Reports can output variables, so you can use that in a report, too.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

This code is suitable for Forms. What about the reports?

Don't understand. The code has got nothing to do with forms or reports. You asked how to get the counts into a variable. My code does that.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[ameedoo3000]

Sorry .. My question was clear and direct.. Is it possible to find the count of the groups in a given field and put it in a variable to put the value of this variable in a text box in form? and The answer was enough from both Mike and Tore Bleken. now I want to put the value of the count of the groups in a text box in a report ? can this SQL code do that or not? This is precisely my destination.
Note:I am from the beginning asking about the count of the groups in one field only.

 

[ameedoo3000]

For example

 

[Olaf Doschke]

Creating the array in command1 click makes it vanish after the click event is over.
You have to create the variables in the context where you do the DO REPORT FORM command.

And then it's simply enmployees as expression, the variable name. To ensure the variable is taken, and not an employees field from any table, you could name your variable better, but mostly ensure the report control should address a variable by m.varname, so m.employees

In short: The variable has to be in scope (exist and not be released already) when you call the report
You simply use the variable name as report control expression, as with any report variable you just define in the report and not beforehand.

If you have trouble getting your array in scope when the report runs, you can also select INTO CURSOR and use that as report control expression. As it's not the workarea that's scanned, you will have a constant single value you could print at any place in the report, just ensure the report workarea is still what you want to print, mainly.

SELECT COUNT(Distinct Field_name) as employees from table_name into cursor crsReportDetail

=> report contorl Expression = crsReportDetail.employees

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[ameedoo3000]

Thank you very very much .. I still learn many and many of you all .. It is a great honor and great to me.