×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

sql select count for myfield=mycondition

sql select count for myfield=mycondition

sql select count for myfield=mycondition

(OP)
Hi,

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

CODE --> vfp9

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

RE: sql select count for myfield=mycondition

You can

CODE

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

RE: sql select count for myfield=mycondition

(OP)
Olaf,

Works like a charm.
Thanks.

Teach me why do you

CODE --> vfp9

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


Regards,
Koen

RE: sql select count for myfield=mycondition

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

RE: sql select count for myfield=mycondition

(OP)
Mike,
Olaf,

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

Thanks,

Koen

RE: sql select count for myfield=mycondition

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

CODE

Select Count(*) From yourtable WHERE score=3 

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: sql select count for myfield=mycondition

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

RE: sql select count for myfield=mycondition

(OP)
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.

CODE --> vfp9

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

RE: sql select count for myfield=mycondition

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:

CODE

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

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close