Subtracting via COUNT?

[jbachris]

Hey,

I've got code that counts the number of entries in the database for each person and then gives me a number. That number should be considered their "Total Appearances" in games. So, 5 games into the season Player A is in the database table 'match_squad' 5 different times, so he has appeared in all 5 matches.

Also in the match_squad table is a match_sub_on field that is either a 1 or a 0. If it is a 0 that means the person was not a sub, but if it is a 1 that means the person was a sub.

Currently, I'm using:

COUNT(match_squad.player_id) as all_app

Which would count all of the player's total appearances in the matches. This works. Also, I'm using

COUNT(match_squad.player_id) as all_sub WHERE match_sub_on = 1

This works as well as far as counting how many times he was a sub. What I am trying to do now is to get the difference. I want to list the total appearances (which I'm already doing), list the sub appearances (which I am already doing), and list the starting appearances.

How can I do this?

Basically I need to subtract all_sub from all_app.

all_app - all_sub = all_start

Thanks!

[esukf]

Try

SELECT match_squad.player_id, (COUNT(match_squad.player_id) - all_sub.subcount)
 AS all_start FROM match_squad INNER JOIN (SELECT player_id, COUNT(player_id) as
 subcount FROM match_squad WHERE match_sub_on = 1 GROUP by 
player_id)all_sub ON match_squad.player_id = all_sub.player_id GROUP BY 
match_squad.player_id;