sql select count for myfield=mycondition

[Koen Piller]

Hi,

I would like to create a cursor with a 'count for myfield = mycondition as mycount'

SELECT club, count(club) as games, COUNT(FOR (score=3)) as wingames, SUM(score) as score GROUP BY club FROM temp ORDER BY 3 descending INTO CURSOR temp2

is that possible?

Regards,

Koen

 

[Olaf Doschke]

You can

SELECT club, count(club) as games, COUNT(EVL(score=3,Cast(.NULL. as L))) as wingames, SUM(score) as score GROUP BY club FROM temp ORDER BY 3 descending INTO CURSOR temp2

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Koen Piller]

Olaf,

Works like a charm.
Thanks.

Teach me why do you

(EVL(score=3,Cast(.NULL. as L)))

Regards,
Koen

 

[Mike Lewis]

Koen,

EVL() changes an empty value to a non-empty value. "Score = 3" evaluates to .T. (which is non-empty) if the score is 3; otherwise, "score = 3" is .F. which empty, and EVL then substitutes it for a logical NULL.

Now, the Count() function only counts non-null values. So it will only count those rows for which Score is 3. Which is what you want.

Hope this makes sense.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Koen Piller]

Mike,
Olaf,

Yes it makes sense.
Pity there is no 'count for'

Thanks,

Koen

 

[Olaf Doschke]

Well, FOR is about the WHERE clause, so you could also easily do

Select Count(*) From yourtable WHERE score=3

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

To be clear, you could also use several COUNT FOR commands on your tables, but you can make several counts all in one scan through all data in one SQL query. Also COUNT FOR doesn't work with GROUPS.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Koen Piller]

Olaf,
I can get the where to be used in this case, since I have three different score's / columns to be populated from the same cursor, your solution with 'Cast(score = 3' etc. works like a charm.

SELECT ;
club, ;
count(club) as games, ;
COUNT(EVL(score=3,Cast(.NULL. as L))) as win, ;
COUNT(EVL(score=1,Cast(.NULL. as L))) as draw, ;
COUNT(EVL(score=0,Cast(.NULL. as L))) as lost, ;
SUM(scorepositive) as scoremade, ;
SUM(scorenegative) as scoreagainst, ;
SUM(scorepositive) - SUM(scorenegative) as balance, ;
(SUM(scorepositive) - SUM(scorenegative))  / count(club) as averagebalance, ;
SUM(score) as score ;
GROUP BY club FROM temp ORDER BY 10 descending, 8 descending INTO CURSOR temp2  

BROWSE normal

Thanks again

Koen

 

[Olaf Doschke]

Yes, that's what I meant with saying this all can be done with a single query.

Code like three COUNTs would be simpler, but iterate the data three times:

COUNT TO nWin FOR FOR score=3
COUNT TO nDraw FOR FOR score=1
COUNT TO nLost FOR FOR score=0

And since that doesn't group by clubs you'd need

Select club, count(*) as wins from temp Where score=3 Group by club
Select club, count(*) as draw from temp Where score=1 Group by club
Select club, count(*) as lost from temp Where score=0 Group by club

And then would still not have a summary in one record per club.

So the base recipe to "emulate" the FOR count is to make the for-condition a boolean expression you count and turning the .F. to .NULL. via EVL(). Looks more complicated than it is, especially in comparison with the "normal" queries, which you'd now need to join by an inner join on the club to get there without such trickery. Written out that becomes even more convoluted than the EVL() expression. The CAST is necessary because of the strictness of the VFP SQL engine to not allow an untyped .NULL., the value stays .NULL. and isn't counted anyway, but the engine insists on it to be the same type as in the other case of score=n being .T., so you need a CAST as L.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name