CODEWITHSUNDEEP
phpmysqlsum
Aryan
Aryan

Reputation: 95

MySQL - Sum of one column in one table with other column in other table

I Have two tables

Table1 is

    idno         marks
     1             12
     1             13
     1             22 
     2             32 
     2             35
     2             11  and so on

Table2 is 

    idno          marks
     1              16
     1              22 
     1              21 
     2              35 
     2              16
     2              22   and so on

I am providing a form for user to enter the idno and submit

If user enters "1" in form and submit then the output should be 

        Total Marks
            106

i.e the sum of all marks of Idno 1 in Table1 + sum of all marks of Idno 1 in table2

(12+13+22)+(16+22+21) = 106

and i am using the following code

<form id="form" action="sum.php" method="post">
<td><p align="center"> IDNO : <input type="text" name="id" id="id" maxlength="10"></p></td>
<input type="submit" id="submit" class='btnExample' value="Click here to get your Result"
</form>

<?PHP
$user_name = "admin";
$password = "123456";
$database = "demo";
$server = "localhost";
$db_handle = mysql_connect($server, $user_name, $password);
$db_found = mysql_select_db($database, $db_handle);

if ($db_found) {
$id = mysql_real_escape_string($_POST['id']);
$add = "SELECT htno, SUM(tech) 
FROM(SELECT htno, SUM(tm) AS tech FROM jbit WHERE htno='$id'
UNION ALL  
SELECT htno, SUM(tm1) AS tech  FROM hmm WHERE htno='$id') AS tech4 "; 
$result3 = mysql_query($add);
echo "
<center><table id='mytable' cellspacing='0'  border=3 align=center>
<tr>
<TH scope='col'>Total Marks</TH>
</tr><center>";
while ($row1 = mysql_fetch_assoc($result3)){
echo "<tr>";
echo "<td align=center>" . $row1['tech4']. "</td>";
echo "</tr>";
}
mysql_close($db_handle);
else {
print "Database NOT Found ";
mysql_close($db_handle);
}

but output is blank

please help me out

Upvotes: 3

Views: 1753

Answers (3)

Aryan
Aryan

Reputation: 95

SELECT SUM(tech) AS tech4 FROM (
(SELECT SUM(tm) AS tech FROM jbit WHERE htno='$id')
UNION ALL
(SELECT SUM(tm) AS tech FROM hmm WHERE htno='$id')
) t1

Upvotes: 0

Ertun&#231;
Ertun&#231;

Reputation: 829

You seem to be missing GROUP by htno in both parts of the union after WHERE clauses. Or you may consider removing htno field from SELECT clauses of the union.

 select sum(sm) from
   (select Sum(marks) sm from sums1 where idno=1
   union
   select Sum(marks) sm from sums2 where idno=1 ) ss

Tested this on mysql and it works

Upvotes: 1

NullPoiиteя
NullPoiиteя

Reputation: 57312

WHERE htno='$id) AS tech4 "; 
               ^----here is the problem  should be htno='$id'

Upvotes: 1

Related Questions