如何从显示的24个值中获得总分。 我是否添加第二个选择语句或是否对分数求和

Question

I have the query below which produces the scores for a user for 24 questions and loads them into a table showing the value for each of the 24 questions (issues).

I want to also show a total of the scores for the same user.

I would like to either add into my current statement, a second SELECT statement about SUM? or add on a statement that adds up the 24 values - but I can't work out how to do either. All the SELECT SUM() statements I have looked at are not working with 4 tables.

Very much appreciated if you can help, thanks, Rosie

//set up the select query
<?
$query = "SELECT a.user_id, a.q1, a.q2, a.q3, a.q4, a.q5, a.q6, b.q7,
 b.q8,b.q9, b.q10, b.q11, b.q12, c. q13, c. q14, c.q15, c.q16,c.q17, 
 c. q18, d. q19, d. q20, d. q21,d. q22, d. q23, d.q24
 FROM book a, magazine b, newspaper c,
 online d
 WHERE a.user_id = b.user_id 
 AND b.user_id = c.user_id 
 AND c.user_id = d.user_id
 ORDER BY user_id DESC";


 // connect to a database and create a query

 $result = mysql_db_query($db,$query);
 $row = mysql_fetch_array($result);
 $list=array();

 $a = $row[1]; 
 $b = $row[2];
 $c = $row[3];
 $d = $row[4];
 $e = $row[5];
 $f = $row[6]; 
 $g = $row[7];
 $h = $row[8];
$i = $row[9];
$j = $row[10];
$k = $row[11]; 
$l = $row[12];
$m = $row[13];
 $n = $row[14];
 $o = $row[15];
 $p = $row[16]; 
 $q = $row[17];
 $r = $row[18];
 $s = $row[19];
 $t = $row[20];
 $u = $row[21]; 
 $v = $row[22];
 $w = $row[23];
 $x = $row[24];

$list = array('issue 1'=>$a,
'issue 2'=>$b,'issues 3'=>$c,'issue 4'=>$d,'issue 5'=>$e,'issue 6'=>$f,
'issue 7'=>$g,'issue 8'=>$h,'issue 9'=> $i,
'issue 10'=> $j, 'issue 11'=> $k,'issue 12'=>$l,'issue 13'=>$m,'issue 14'=>$n,
'issue 15'=>$o,'issue 16'=>$p,'issue 17'=>$q,'issue 18'=>$r,'issue 19'=>$s,
'issue 20'=>$t, 'issue 21'=> $u,'issue 22'=> $v,'issue 23'=>$w,'issue 24'=>$x);
?>
<div class = "container">
<?            
print "<table border=5 align= \"center\" width=\"1017\"  bgcolor=\"BD9DFF\">";
print "<tr><td><h2>Question </h2></td><td><h2>Score  
 </h2></td></tr>";
arsort($list);
foreach ($list as $key => $val)
{ 
//set up the table and headings
print "<tr><td><h2>$key</h2></td><td><h2>$val</h2></td></tr>";
} 
//close the table
{
 print "</table>";
}
?>

Answer 1

Add them with the + operator:

SELECT a.user_id, a.q1, a.q2, a.q3, a.q4, a.q5, a.q6, b.q7,
       b.q8,b.q9, b.q10, b.q11, b.q12, c.q13, c.q14, c.q15, c.q16,c.q17, 
       c.q18, d.q19, d.q20, d.q21,d.q22, d.q23, d.q24,
       (a.q1+a.q2+a.q3+a.q4+...+d.q24) AS total
FROM book a
JOIN magazine b ON a.user_id = b.user_id
JOIN newspaper c ON b.user_id = c.user_id
JOIN online d ON c.user_id = d.user_id
ORDER BY a.user_id DESC

SUM() is used for adding values across multiple rows. To add columns in the same row, you just add them.

The total will be in $row['total']. BTW, I recommend you get out of the habit of using the numeric indexes, use the named indexes, e.g. $row['user_id'] instead of $row[0].