2

Simple to say, but I can't find the syntax or even an example even close! Assume the following tables:

Table:'Red'
Fields: id | sm | md | lg
Data:    1 | 3  | 5  | 7
         2 | 9  | 8  | 7
         3 | 2  | 4  | 6

Table:'White'
Fields: id | sm | md | lg
Data:    1 | 0  | 0  | 0
         2 | 0  | 0  | 0
         3 | 0  | 0  | 0

Table:'Blue'
Fields: id | sm | md | lg
Data:    1 | 1  | 1  | 1
         2 | 1  | 1  | 1
         3 | 1  | 1  | 1

All i want is to total everything up, but keep the rows like the following table:

Table:'Total'
Fields: id | sm | md | lg
Data:    1 |  4 | 6  | 8
         2 | 10 | 9  | 8
         3 |  3 | 5  | 7

Then create a while loop in PHP to echo back the results. Something like this:

<?php

while($row = mysql_fetch_array($get_totals))
{
echo <td>".$row[sm]."</td><td>".$row[md]."</td><td>".$row[lg]."</td>";
}

?>

I can't figure this out. Any help? I just need a php select statement that will work here.

user1261388
  • 55
  • 1
  • 1
  • 9

1 Answers1

2

Not tested but should work:

SELECT id, SUM(sm) as sm, SUM(md) as md, SUM(lg) as lg FROM (
   SELECT * FROM Red
   UNION ALL
   SELECT * FROM White
   UNION ALL
   SELECT * FROM Blue
) AS somealias 
GROUP BY id
nickb
  • 59,313
  • 13
  • 108
  • 143
WojtekT
  • 4,735
  • 25
  • 37
  • Added an alias to the query as per [this SO question](http://stackoverflow.com/questions/1888779/every-derived-table-must-have-its-own-alias). I've tested this solution and it does indeed work. – nickb Jun 07 '12 at 00:10
  • Right @nickb, forgot about that. – WojtekT Jun 07 '12 at 00:10