count records and save it in a table

[keepfoxing]

i have a table with two columns with gender and total field.
what i want is the table to display two records which is male and female with
the total number of each.

or is it better to save it to another table?

here is the data input..
name | gender
--------------
doe | male
john | male
eva | female

and this is what i want to display..
gender | total
---------------
male | 2
-------|-------
female | 1


thanks in advance..

 

[DSummZZZ]

Personally, I wouldn't want to contaminate the original data with extra records for counts. Is there some reason you don't want to use an extra table - or even a cursor - for counts?
Try this:
SELECT gender, cnt(gender) AS nHowMany FROM yourtable INTO CURSOR counts GROUP BY gender
SELECT gender
BROWSE


-Dave Summers-

Even more Fox stuff at:

http://www.dave-summers.net/foxprolinks.htm

 

[keepfoxing]

in addition, the table should be on the form...
i tried this but for male only..

SELECT Count(gender.gender) from gender WHERE gender = "Male"

 

[Dan Freeman]

If you have a table or cursor named gender that query should have worked, although I would have named the output column and the destination cursor. Otherwise, all you get is a browse window.

SELECT Count(gender.gender) [b]As Howmany[/b] from gender WHERE gender = "Male" [b]Into Cursor Males[/b]

Once you have the data into a usable form it's up to you to put it on a form.

 

[Mike Lewis]

If I've understood it right, what you want is this:

[tt]SELECT Gender, COUNT(Gender) AS HowMany FROM MyTable GROUP BY Gender INTO TABLE Results[/tt]

This will create a new table, named Results, which will look something like this:

Gender     HowMany
======     =======
male       53
female     127

Of course, you can also send the results to a cursor, array, etc.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I'd do as Mike just put the result INTO CURSOR and display that. If you index the gender column of the input data the query will be fast and you won't need to maintain an extra table for counts, that's redundancy and redundant data is doomed to be wrong at some time. You might say you can update the count with each insert or update, but then you'd forget that at one place or not do it, when browsing input and changing something on the fly. The schema of the original table also has one flaw, you can easily have spelling erros or upper lower case differences. Is it political correct to not just have a boolean field for genders, today? Maybe, but I'd say for most cases you don't need to know the details... So my design for such a table might be cName, iGender with an int in range 0 to 2 for 0=unknown, 1=female, 2=male. Bound to an optiongroup with the two options female and male the 0 will be no option selected.

To get a name with the group by query you can again bind the result to such an optiongroup or use ICase():

SELECT ICase(iGender=1,"Female",iGender=2,"Male ","Unknown") as Gendername, Count() as Howmany From inputdata Group By 1

You may or may not add a field rule only allowing int values 0,1, and 2.

*Sample data
Create Cursor inputdata (iID I AutoInc, cName C(50), iGender I Check iGender>=0 AND iGender<=2 ERROR "Wrong Gender" Default 0)
Insert into inputdata (cName,iGender) Values ("Mary",1)
Insert into inputdata (cName,iGender) Values ("Bob",2)
Insert into inputdata (cName,iGender) Values ("Intern",0)

SELECT ;
     Cast(ICase(iGender=1,"Female",iGender=2,"Male","Unknown") as Char(7)) as cGendername ;
   , Count(*) as iHowmany ;
FROM inputdata ;
   GROUP BY iGender ;
INTO CURSOR crsResult

It's part of the field definition (ideally put into the field comment), what 0, 1, and 2 mean. It's "clear" from the UI, if you always bind it to such an optiongroup, but it's not clear from the data alone, so it's better documented.
Group By iGender can be optimized better than Group By 1, but needs the value range restriction, otherwise several result records will have cGendername "Unknown" and still have a record each due to different iGender values, that's restricted with the CHECK (the table designer has this as field rule). A cursor can't have a field comment/description, so I skipped that part, but a table can have comments for the whole table and fields.

So even this little thing leaves you with some decisions and exercises. If you think it through to the end, you'll not need to come back later.

Bye, Olaf.

 

[keepfoxing]

sir olaf,
i use your code and modified it..i removed the 1st 5 lines
and created a table..

i got an error of: operator/operand mismatch
i think it is because of the gender variable which i set to character.

Cast(ICase(iGender=1,"Female",iGender=2,"Male","Unknown") as Char(7)) as cGendername ;

any help will be appreciated..

 

[Olaf Doschke]

Well, the query is for a table with an integer iGender field, yes. You don't need an expression translating from 0,1,2 to the gender names, if you store gender names, but I recommend changing your source data table, because it's no good idea to have a char field to store the gender "female", "male", as such a filed is doomed to contain "Female", "Male", "FEMALE", "MALE", "UNKNOWN" or anything else but the valid values.

If you want to stay with your design use the query Mike gave.

Bye, Olaf.