Displaying a running total on my website?

Question

Hi guys,

I'm having a little trouble working out how to display a running total of sales that have gone through my website. I have no problem working out how to do this with a single output from one table, but unfortunately for my website the results of total sales are spread across 2 tables in the database.

I normally get the total sales manually using a Union query in SQL (whenever I feel like checking it). I am trying to devise a means of displaying the total figure on my website as a single number formatted with comma's for thousands.

So far I have managed to create some code which displays the total's from each table as a separate figure. Apart from the 2 separate figures, this is perfect. All I need to do is join up the 2 rows of output into a single figure. The code I currently have is below:

$sql= "SELECT sum(price) AS total_price FROM `table_A` 
UNION ALL
SELECT sum(price) AS total_price FROM `table_B`";
$res= mysql_query($sql);
if (mysql_num_rows($res)>0) {
  while ($row=mysql_fetch_array($res)) {
echo '<h3>'.number_format($row["total_price"], 0).'</h3>';
  };
};
echo mysql_error();

Can anyone help me with this problem?

Answer 1

Just add them up:

$sql = <<<END
SELECT sum(price) AS total_price FROM `table_A` 
UNION ALL
SELECT sum(price) AS total_price FROM `table_B'
END;
$res = mysql_query($sql);
if (!$res) {
  die('Error: ' . mysql_error() . ' in query ' . $sql);
}
$total = 0;
while ($row = mysql_fetch_array($res)) {
  $total += $row[0];
}
$total = number_format($total, 0);
echo '<h3>Total Sales are $' . $total . '</h3>';

Alternatively, it can be done in one query:

SELECT SUM(total_price) FROM (
  SELECT sum(price) AS total_price FROM `table_A` 
  UNION ALL
  SELECT sum(price) AS total_price FROM `table_B'
)

Personally I'd just add it in code but either works.

Answer 2

SELECT sum(price) AS total_price FROM
(SELECT price FROM table_A
 UNION
 SELECT price FROM table_B
)

Answer 3

It seems like the only thing you have left to do is take the sum of these values.

 $sum = 0;
 while ($row=mysql_fetch_array($res)) {
     $sum += $row["total_price"]
 };
 echo '<h3>'.number_format($sum, 0).'</h3>';

Answer 4

This will return a single figure as the sum of the values from both tables.

   SELECT 
    (SELECT sum(price) AS total_price FROM `table_A`) +
        (SELECT sum(price) AS total_price FROM `table_B`);

Well maybe, you get the idea.