[RESOLVED] Adding two COUNTs together in one query

NiallThistle

I have the following code:

$get_player_totals = mysql_query("SELECT 
COUNT(A.AppearancePlayerID) + COUNT(S.SubstitutionPlayerIDIn) AS totalapps,
P.PlayerID AS playerid,
P.PlayerLastName AS player
FROM player_appearances A, player_substitutions S, players P
WHERE P.PlayerID = A.AppearancePlayerID
AND P.PlayerID = S.SubstitutionPlayerIDIn 
GROUP BY 'playerid'
ORDER BY 'totalapps' DESC
", $connection) or die(mysql_error());

What it's meant to do is combine full appearances with substitute appearances of football players and display them as one total. At the moment I get a result but its an astronomically incorrect total for each player.

The tables are built as follows:

player_appearances (2 fields - AppearanceID & AppearancePlayerID)
player_substitutions (2 fields - SubstitutionID & SubstitutionPlayerIDIn)
players (3 fields - PlayerID, PlayerFirstName, PlayerLastName)

Ideally, I want to display rows for each player, with the following 2 columns:

Name | Total Apps (Apps and Subs)

Total Apps with the sum of all of their appearances and substitutions.

Where am I going wrong in combining the two counts, and if there is a better way, could someone please provide an example that allows me to sort the rows by the total apps?

Thanks in advance.

johanafm

Your joins take
1 row - a player

combine that with every full appearance that player made, let's say N appearances = N rows

combine each of those N rows with every partial appearance that player made, say M rows

Result: Player has appeared in M*N games

For fairly small numbers of M and N, that's more appearances than there are actual games.

Solution:
1. Count full appearances
2. Count partial appearances
3. Sum them up.

johanafm

Your joins take
1 row - a player

combine that with every full appearance that player made, let's say N appearances = N rows

combine each of those N rows with every partial appearance that player made, say M rows

Result: Player has appeared in M*N games

For fairly small numbers of M and N, that's more appearances than there are actual games.

Solution:
1. Count full appearances
2. Count partial appearances
3. Sum them up.

Simple example, which removes the player table and only uses one player

SELECT SUM(a)
FROM (
	SELECT COUNT(*) AS a
	FROM full
	WHERE player_id=@id
	UNION
	SELECT COUNT(*)
	FROM partial
	WHERE player_id=@id
) AS t;

Should be fairly simple to readd the player table and make it work for all players at once.

NiallThistle

Thank you.

I've adapted what I have to read:

$get_player_totals = mysql_query("
SELECT SUM(apps)
FROM (
	SELECT COUNT(*) AS apps
	FROM player_appearances
	WHERE AppearancePlayerID = 152
	UNION
	SELECT COUNT(*)
	FROM player_substitutions
	WHERE SubstitutionPlayerIDIn = 152
) AS total
", $connection) or die(mysql_error());

But I'm getting an SQL syntax error: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(*) AS apps FROM player_appearances WHERE Appearan"

Any ideas where I've gone wrong?

NiallThistle

Still no further forward with this. FYI, the database version is MySQL 4.0, if that has any impact on it.

bradgrafelman

NiallThistle;11017151 wrote:
the database version is MySQL 4.0, if that has any impact on it.

Using such an ancient version would indeed have an impact, considering you're rather limited in the types of queries you can (or, more aptly, can not) do.

Why use such an old version? Do you write your code using a typewriter too? 😉

NiallThistle

Is that why I'm having problems?

It's the database provided by the host, unfortunately.

NiallThistle

Okay, I have managed to upgrade to MySQL 5.0 and the query now works, for the instance when I know which PlayerID I want the results of.

Next question - how do I modify this query to group by the PlayerID to show the results for all players?

Weedpacket

MySQL didn't support derived tables until version 4.1 (released in 2003).

Okay, I have managed to upgrade to MySQL 5.0

Support for MySQL 5.0 ended earlier this year; the current version is 5.5.