MySQL UNION with the alias COUNT

Question

I am trying to extract two accounts from two separate tables into one SQL query for use with PHP. This is the current SQl request:

SELECT COUNT(entryid) AS total FROM rh_entries UNION SELECT COUNT(pentryid) AS attended FROM rh_playerentries WHERE playerid=79 

This is the PHP that I use to use the data: $ result = mysql_query ($ query);

 $attendance = mysql_fetch_assoc($result); echo "Total: " . $attendance['total'] . " Attended: " . $attendance['attended'] . " Percentage: " . (int)$attendance['total'] / (int)$attendance['attended'] . " <br />"; 

I get this output:

 Warning: Division by zero in /home/content/g/V/i/gViscardi/html/guilds/sanctum/raidinfo/player.php on line 41 Total: 6 Attended: Percentage: 

Apparently, $ attendance ['visited'] is not set properly. Am I missing something, how does UNION or COUNT or AS work?

Answer 1

The union combines the contents of two queries into one table. Your queries have different column names, so merging will not work properly. You need something like:

 SELECT (SELECT COUNT(entryid) FROM rh_entries) as total, (SELECT COUNT(pentryid) FROM rh_playerentries WHERE playerid=79) as attended; 

Answer 2

To expand the tip of amccausl, after you have this single table, you can perform operations on it:

 select sum(total) from ( SELECT COUNT(entryid) FROM rh_entries as total UNION SELECT COUNT(pentryid) as total FROM rh_playerentries WHERE playerid=79 ) 

Answer 3

 select sum(total) from ( SELECT COUNT(entryid) total FROM rh_entries UNION SELECT COUNT(pentryid) total FROM rh_playerentries WHERE playerid=79 ) as t;