SQL Multiple Result question....

Question

Can I use a single SQL statement to return three separate results I can display through PHP? I'm looking to formulate three different "total" counts on a single table with three different conditions.

Eg something like....

$resultGetTotals = SELECT COUNT(Total1) FROM table_xyz WHERE Status = X AS Total1
SELECT COUNT(Total2) FROM table_xyz WHERE Status = X AND Person = Y AS Total2
SELECT COUNT(Total3) FROM table_xyz WHERE Status = Y AND Person = X AS Total3

while ($rGetTotals = mysql_fetch_array($resultGetTotals)){
$Total1 = $rGetTotals["Total1"]
$Total2 = $rGetTotals["Total2"]
$Total2 = $rGetTotals["Total2"];
}

Total One is: <?php print $Total1; ?><br>
Total Two is: <?php print $Total2; ?><br>
Total Three is: <?php print $Total3; ?><br>

Any help greatly appreciated :)

Answer 1

SQL:

SELECT SUM(CASE WHEN t.status = X THEN 1 ELSE 0 END) AS total1,
       SUM(CASE WHEN t.status = X AND t.person = Y THEN 1 ELSE 0 END) AS total2,
       SUM(CASE WHEN t.status = Y AND t.person = X THEN 1 ELSE 0 END) AS total3
  FROM TABLE_XYZ t

PHPified:

$result = mysql_query("SELECT SUM(CASE WHEN t.status = X THEN 1 ELSE 0 END) AS total1,
                              SUM(CASE WHEN t.status = X AND t.person = Y THEN 1 ELSE 0 END) AS total2,
                              SUM(CASE WHEN t.status = Y AND t.person = X THEN 1 ELSE 0 END) AS total3
                         FROM TABLE_XYZ t");

while ($row = mysql_fetch_array($result)) {
 echo "Total One is: $row['total1']<br>"
 echo "Total Two is:  $row['total2']<br>"
 echo "Total Three is:  $row['total3']<br>"
}

mysql_free_result($result);

Answer 2

Assuming you want the number of rows for each user (and not the number of non-null values in three different columns), and then for all users, and further assuming that there are only Person X and Y in the table, use:

SELECT Person, COUNT(*) FROM table_xyz GROUP BY Person

Retrieve the two totals (for person X and person Y) and add them together to get the grand total.