SUM() across multiple tables

hi guys… ( i’m from malaysia )

I am trying to do 2 sums in this mysql query from 3 table using lef join.

[1st table]
log_pensyarah (lecturer)

[2nd table]
log_dekan_vote (department officer)

[3rd table]
log_pelajar_vote (student)

this is my code

<?php

$query = ("SELECT * FROM log_pensyarah LEFT JOIN log_dekan_vote ON log_pensyarah.pensyarah = log_dekan_vote.pensyarah
           LEFT JOIN log_pelajar_vote ON log_dekan_vote.pensyarah = log_pelajar_vote.pensyarah
           WHERE (SELECT SUM(grandtotal) FROM log_dekan_vote GROUP BY 'pensyarah') AND (SELECT SUM(total) FROM log_pelajar_vote GROUP BY 'pensyarah')
           ORDER BY 'id'");
$result = mysql_query($query) or die(mysql_error());


?>


        <table border="0" cellpadding="0" cellspacing="0" id="results">
        <tr bgcolor="#B0D2E9">
        <td class=tabhead width="150" rowspan="2"><span class="tabhead">&nbsp;<b>Nama Pensyarah</b></span></td>
        <td class=tabhead width="150" rowspan="2"><span class="tabhead">&nbsp;<b>No IC</b></span></td>
        <td class=tabhead width="200" rowspan="2"><b>Kursus Dicerap</b></td>
        <td class=tabhead colspan="4"><b>Skor [PK(O).JPPKK.09(L1)]</b></td>
        <td class=tabhead width="141" rowspan="2"><b>JUMLAH L1 (%)</b></td>
        <td class=tabhead width="24" rowspan="2"><b>Skor  [PK(O).JPPKK.09(L2)]<br />(%)</b></td>
  </tr>
       <tr bgcolor="#B0D2E9">
       <td width="49"><b>A</b></td>
       <td width="49"><b>B</b></td>
       <td width="46"><b>C</b></td>
       <td width="44"><b>D</b></td>
  </tr>
<?php


                $i = 0;
                while ($row = mysql_fetch_array($result)) {
                     if ($i > 0 ) {
                        echo "<tr valign='bottom'>";
                        echo "</tr>";
                    }

                    if($_SESSION['id'])
                    echo "<tr class='alt' valign='middle'>";
                    if($_SESSION['id'])
                    echo "<td class='tabval'>".$row['pensyarah']."</td>";
                    if($_SESSION['id'])
                    echo "<td class='tabval2'>".$row['noic']."</td>";
                    if($_SESSION['id'])
                    echo "<td class='tabval'>".$row['kksem']."</td>";
                    if($_SESSION['id'])
                    echo "<td class='tabval2'>".$row['total_a']."</td>";
                    if($_SESSION['id'])
                    echo "<td class='tabval'>".$row['total_b']."</td>";
                    if($_SESSION['id'])
                    echo "<td class='tabval2'>".$row['total_c']."</td>";
                    if($_SESSION['id'])
                    echo "<td class='tabval2'>".$row['total_d']."</td>";
                    if($_SESSION['id'])
                    echo "<td class='tabval2'>".$row['grandtotal']."</td>";
                    if($_SESSION['id'])
                    echo "<td class='tabval2'>".$row['total']."</td>";
                    if($_SESSION['id'])
                    echo "</tr>";
                    
                   else echo '<h3>Anda tidak mempunyai kebenaran untuk mengakses halaman ini. Sila kembali <a href="index.php">ke halaman utama</a></h3>';
                    $i++;

                }

                echo "<tr valign='bottom'>";
                echo "</tr>";
                
                mysql_close();
            ?>
        </table>

i want to get the total sum from 2nd and 3rd table… but i get stuck with the sql statement…

e.g final result table (didn’t work)

it supose to be like this

Any help would be greately appreciated.
Thanks

Sory for my english, i’m from malaysia.

SELECT * 
FROM log_pensyarah 
LEFT JOIN log_dekan_vote 
ON log_pensyarah.pensyarah = log_dekan_vote.pensyarah
LEFT JOIN log_pelajar_vote 
ON log_dekan_vote.pensyarah = log_pelajar_vote.pensyarah
WHERE (SELECT SUM(grandtotal) FROM log_dekan_vote GROUP BY 'pensyarah') AND (SELECT SUM(total) FROM log_pelajar_vote GROUP BY 'pensyarah')
 ORDER BY 'id'

The WHERE statements in your query make no sense to me :confused:
Please explain in your words what you are trying to do. What sums do you want to calculate.

‘I want to get the total sum from 2nd and 3rd table’ doesn’t explain anything. What exactly do you want to sum from those 2 tables?

SELECT p.pensyarah
     , p.noic
     , dv.grandtotal
     , pv.total
  FROM log_pensyarah AS p
LEFT OUTER
  JOIN ( SELECT pensyarah
              , SUM(grandtotal) AS grandtotal
           FROM log_dekan_vote 
         GROUP
             BY pensyarah ) AS dv
    ON dv.pensyarah = p.pensyarah            
LEFT OUTER
  JOIN ( SELECT pensyarah
              , SUM(total) AS total
           FROM log_pelajar_vote 
         GROUP
             BY pensyarah ) AS pv
    ON pv.pensyarah = p.pensyarah            
ORDER 
    BY p.id

Sory i miss that…actually the code is like this

$query = ("SELECT * FROM log_pensyarah LEFT JOIN log_dekan_vote ON log_pensyarah.pensyarah = log_dekan_vote.pensyarah
           LEFT JOIN log_pelajar_vote ON log_dekan_vote.pensyarah = log_pelajar_vote.pensyarah
           WHERE (SELECT pensyarah, SUM(grandtotal) FROM log_dekan_vote GROUP BY 'pensyarah') AND (SELECT pensyarah, SUM(total) FROM log_pelajar_vote GROUP BY 'pensyarah')
           ORDER BY 'id'");
$result = mysql_query($query) or die(mysql_error());
?>

i want to count the sum total of [total] from log_pelajar_vote table and sum total of [grandtotal] from log_dekan table group by pensyarah.

And how actually the correct way to display the result table instead this

<table border="0" cellpadding="0" cellspacing="0" id="results">
        <tr bgcolor="#B0D2E9">
        <td class=tabhead width="150" rowspan="2"><span class="tabhead">&nbsp;<b>Nama Pensyarah</b></span></td>
        <td class=tabhead width="150" rowspan="2"><span class="tabhead">&nbsp;<b>No IC</b></span></td>
        <td class=tabhead width="200" rowspan="2"><b>Kursus Dicerap</b></td>
        <td class=tabhead colspan="4"><b>Skor [PK(O).JPPKK.09(L1)]</b></td>
        <td class=tabhead width="141" rowspan="2"><b>JUMLAH L1 (%)</b></td>
        <td class=tabhead width="24" rowspan="2"><b>Skor  [PK(O).JPPKK.09(L2)]<br />(%)</b></td>
  </tr>
       <tr bgcolor="#B0D2E9">
       <td width="49"><b>A</b></td>
       <td width="49"><b>B</b></td>
       <td width="46"><b>C</b></td>
       <td width="44"><b>D</b></td>
  </tr>
<?php


                $i = 0;
                while ($row = mysql_fetch_array($result)) {
                     if ($i > 0 ) {
                        echo "<tr valign='bottom'>";
                        echo "</tr>";
                    }

                    if($_SESSION['id'])
                    echo "<tr class='alt' valign='middle'>";
                    if($_SESSION['id'])
                    echo "<td class='tabval'>".$row['pensyarah']."</td>";
                    if($_SESSION['id'])
                    echo "<td class='tabval2'>".$row['noic']."</td>";
                    if($_SESSION['id'])
                    echo "<td class='tabval'>".$row['kksem']."</td>";
                    if($_SESSION['id'])
                    echo "<td class='tabval2'>".$row['total_a']."</td>";
                    if($_SESSION['id'])
                    echo "<td class='tabval'>".$row['total_b']."</td>";
                    if($_SESSION['id'])
                    echo "<td class='tabval2'>".$row['total_c']."</td>";
                    if($_SESSION['id'])
                    echo "<td class='tabval2'>".$row['total_d']."</td>";
                    if($_SESSION['id'])
                    echo "<td class='tabval2'>".$row['grandtotal']."</td>";
                    if($_SESSION['id'])
                    echo "<td class='tabval2'>".$row['total']."</td>";
                    if($_SESSION['id'])
                    echo "</tr>";
                    
                   else echo '<h3>Anda tidak mempunyai kebenaran untuk mengakses halaman ini. Sila kembali <a href="index.php">ke halaman utama</a></h3>';
                    $i++;

                }

                echo "<tr valign='bottom'>";
                echo "</tr>";
                
                mysql_close();
            ?>
        </table>

i alway get an error like… Operand should contain 1 column(s) when i try to alter the code.

I don’t know why this is happening…Any ideas how to fix the query?

Thank you in advance.

hi r937… thank you for ur quick reply… after i put the code that you gave, i get an error [ You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’’ at line 21 ] any ideas …?

please run the query outside of php to test it properly

yes i get it right… however it didn’t display all the result…

how to pull the [kksem] column from log_dekan_vote and log_pelajar_vote table ?

and sumtotal for [total_a],[total_b],[total_c],[total_d] column from log_dekan_vote table.

i try to do like this

SELECT p.pensyarah
, p.noic
, dv.grandtotal
, pv.total
FROM log_pensyarah AS p
LEFT OUTER
JOIN ( SELECT pensyarah,kksem,total_a,total_b,total_c,total_d, SUM(grandtotal) AS grandtotal
FROM log_dekan_vote
GROUP
BY pensyarah ) AS dv
ON dv.pensyarah = p.pensyarah
LEFT OUTER
JOIN ( SELECT pensyarah,kksem, SUM(total) AS total
FROM log_pelajar_vote
GROUP
BY pensyarah ) AS pv
ON pv.pensyarah = p.pensyarah
ORDER
BY p.id

but still not displaying the result… can you point me where exactly i’m doing wrong…

hi, r937… finaly i can pull the [kksem] , [total_a], [total_b], [total_c], [total_d] column from log_dekan_vote table and [kksem] from log_pelajar_vote.

SELECT p.pensyarah, p.noic, dv.kksem, dv.total_a, dv.total_b, dv.total_c, dv.total_d, dv.grandtotal, pv.kksem, pv.total
FROM log_pensyarah AS p
LEFT OUTER
JOIN (

SELECT pensyarah, SUM( grandtotal ) AS grandtotal, kksem AS kksem, total_a AS total_a, total_b AS total_b, total_c AS total_c, total_d AS total_d
FROM log_dekan_vote
GROUP
BY pensyarah
) AS dv ON dv.pensyarah = p.pensyarah
LEFT OUTER
JOIN (

SELECT pensyarah, SUM( total /400 *100 ) AS total, kksem AS kksem
FROM log_pelajar_vote
GROUP
BY pensyarah
) AS pv ON pv.pensyarah = p.pensyarah
ORDER
BY p.id

thank you so much for all your advice , you help me alots :slight_smile:

hi r937,

is it posible to make search or sort result from left outer join tables ?

im not sure how the query goes and here is the code I am using

<?php

     if (!$row)
     {
     print ("$XX");
     }

?>

        <p>&nbsp;</p>
        
        <form action="<?php echo basename($_SERVER['PHP_SELF']); ?>" method="get">
             <table width="100%">
              <tr>
                <td><table border="0" align="right" cellpadding="0" cellspacing="0">
                  <tr>
                    <td><select name="metode" id="metode" size="1">
                      <option selected="selected">All Data</option>
                      <option value="p.pensyarah">Pensyarah</option>
                      <option value="pv.program">Program</option>
                    </select>
                      <input type="text" name="search" id="search" size="25" />
                      <input class="firmBTN" type="submit" value="Search" /></td>
                  </tr>
                </table></td>
              </tr>
            </table>
        </form>
        
        <p>&nbsp;</p>


        <table border="0" cellpadding="0" cellspacing="0" id="results">
        <tr bgcolor="#B0D2E9">
        <td class=tabhead width="250" rowspan="2"><span class="tabhead">&nbsp;<b>Nama Pensyarah</b></span></td>
        <td class=tabhead width="120" rowspan="2"><span class="tabhead">&nbsp;<b>No IC</b></span></td>
        <td class=tabhead width="200" rowspan="2"><b>Kursus Dicerap</b></td>
        <td height="30" class=tabhead colspan="4"><b>Skor [PK(O).JPPKK.09(L1)]</b></td>
        <td class=tabhead width="141" rowspan="2"><b>JUMLAH L1 (%)</b></td>
        <td class=tabhead width="24" rowspan="2"><b>Skor  [PK(O).JPPKK.09(L2)]<br />(%)</b></td>
  </tr>
       <tr bgcolor="#eae2b4">
       <td align= "center" width="49"><b>A</b></td>
       <td align= "center" width="49"><b>B</b></td>
       <td align= "center" width="46"><b>C</b></td>
       <td align= "center" width="44"><b>D</b></td>
  </tr>
<?php
     $query = ("SELECT p.pensyarah, p.noic, dv.program, dv.kksem, dv.total_a, dv.total_b, dv.total_c, dv.total_d, dv.grandtotal, pv.program, pv.kksem, pv.total
                FROM log_pensyarah AS p
                
                LEFT OUTER JOIN
                (SELECT pensyarah, SUM( grandtotal ) AS grandtotal, program AS program, kksem AS kksem, total_a AS total_a, total_b AS total_b, total_c AS total_c, total_d AS total_d
                FROM log_dekan_vote
                GROUP BY pensyarah)
                AS dv ON dv.pensyarah = p.pensyarah
                
                LEFT OUTER JOIN
                (SELECT pensyarah, SUM( total / 400 * 100 ) AS total, program AS program, kksem AS kksem
                FROM log_pelajar_vote
                GROUP BY pensyarah)
                AS pv ON pv.pensyarah = p.pensyarah
                WHERE 'metode' LIKE '%$search%' LIMIT 0, 50");
    
    $result = mysql_query($query) or die(mysql_error());
    
    $XX = "No Record Found, please search again";


                $i = 0;
                while ($row = mysql_fetch_array($result)) {
                extract($row);

                     if ($i > 0 ) {
                        echo "<tr valign='bottom'>";
                        echo "</tr>";
                    }



                    if($_SESSION['id'])
                    echo "<tr class='alt' valign='middle'>";
                    if($_SESSION['id'])
                    echo "<td align='center' class='tabval'><b>".$row['pensyarah']."</b></td>";
                    if($_SESSION['id'])
                    echo "<td align='center' class='tabval2'>".$row['noic']."</td>";
                    if($_SESSION['id'])
                    echo "<td align='center' class='tabval'>".$row['kksem']."</td>";
                    if($_SESSION['id'])
                    echo "<td align='center' class='tabval2'>".$row['total_a']."</td>";
                    if($_SESSION['id'])
                    echo "<td align='center' class='tabval'>".$row['total_b']."</td>";
                    if($_SESSION['id'])
                    echo "<td align='center' class='tabval2'>".$row['total_c']."</td>";
                    if($_SESSION['id'])
                    echo "<td align='center' class='tabval2'>".$row['total_d']."</td>";
                    if($_SESSION['id'])
                    echo "<td align='center' class='tabval2'>".sprintf("%1\\$.2f",$row['grandtotal'])."</td>";
                    if($_SESSION['id'])
                    echo "<td align='center' class='tabval2'>".sprintf("%1\\$.2f",$row['total'])."</td>";
                    if($_SESSION['id'])
                    echo "</tr>";
                    
                   else echo '<h3>Anda tidak mempunyai kebenaran untuk mengakses halaman ini. Sila kembali <a href="index.php">ke halaman utama</a></h3>';
                    $i++;

                }

                echo "<tr valign='bottom'>";
                echo "</tr>";
                
                mysql_close();
            ?>

the query not function at all and sometimes i get an error like this…

what is this mean ?

I’m still new in Php Mysql but i’m working hard to learn… can you point me, where exactly i’m doing wrong. I already try to figure out almost 30hrs and never sleep yet :sick: If someone could help I’d really appreciate it.

Thank you in advance.

your query looks okay except for the quotes around ‘metode’

Thank you for your quick reply. I try delete the quotes, but i get this

is there any other way you that you can suggest to me ?..

You have no column called metode in the tables you use in your query. What is the name of the column you want to do the search on?

two columns from 2 table

1st table ( log_pensyarah ) ,column ( pensyarah )

2nd table ( log_pelajar_vote ) ,column ( program )

So change metode in p.pensyarah
And add a similar line to the second left outer join with program

sorry, i cant figure out what its mean… can you show me the code …
using from my php code

so… sorry, i’m using a localhost. I’m not sure if this working on hosting server…:sick:

     
$query = ("SELECT p.pensyarah, p.noic, dv.program, dv.kksem, dv.total_a, dv.total_b, dv.total_c, dv.total_d, dv.grandtotal, pv.program, pv.kksem, pv.total
                FROM log_pensyarah AS p

                LEFT OUTER JOIN
                (SELECT pensyarah, SUM( grandtotal ) AS grandtotal, program AS program, kksem AS kksem, total_a AS total_a, total_b AS total_b, total_c AS total_c, total_d AS total_d
                FROM log_dekan_vote
                GROUP BY pensyarah)
                AS dv ON dv.pensyarah = p.pensyarah
               
                LEFT OUTER JOIN
                (SELECT pensyarah, SUM( total / 400 * 100 ) AS total, program AS program, kksem AS kksem
                FROM log_pelajar_vote
                WHERE program  LIKE '%$search%'
                GROUP BY pensyarah)
                AS pv ON pv.pensyarah = p.pensyarah
                WHERE p.pensyarah  LIKE '%$search%' LIMIT 0, 50");

Hi, guido2004
thank you for your generosity, but it still not working …hurm, i wonder why its not working… when i search and choose the keyword from dropdown menu, its nothing happens…its just refresh the page. I’m not sure if there have any other way to do this query… but i dont know where to search it anymore… i’ve been googling for 24 hour just to find the correct way to implemnt this …

Thanks again.

Wait, I see you can choose to do the search on both tables, or on one of them.
So you’ll have to create your query differently according to the value of that field.
I’ll be back to you in a moment :slight_smile:

Add an echo of the $query to your script, copy the result from your browser and paste it into PHPMyAdmin, and see if it gives you any results.
If it doesn’t, copy and paste only the part of the second left join, and see if that gives any results.

     
$query = ("SELECT p.pensyarah, p.noic, dv.program, dv.kksem, dv.total_a, dv.total_b, dv.total_c, dv.total_d, dv.grandtotal, pv.program, pv.kksem, pv.total
                FROM log_pensyarah AS p

                LEFT OUTER JOIN
                (SELECT pensyarah, SUM( grandtotal ) AS grandtotal, program AS program, kksem AS kksem, total_a AS total_a, total_b AS total_b, total_c AS total_c, total_d AS total_d
                FROM log_dekan_vote
                GROUP BY pensyarah)
                AS dv ON dv.pensyarah = p.pensyarah
               
                LEFT OUTER JOIN
                (SELECT pensyarah, SUM( total / 400 * 100 ) AS total, program AS program, kksem AS kksem
                FROM log_pelajar_vote
                WHERE program  LIKE '%$search%'
                GROUP BY pensyarah)
                AS pv ON pv.pensyarah = p.pensyarah
                WHERE p.pensyarah  LIKE '%$search%' LIMIT 0, 50");
echo "query: $query<br/>";

yes, this part

<form action="<?php echo basename($_SERVER['PHP_SELF']); ?>" method="get">
             <table width="100%">
              <tr>
                <td><table border="0" align="right" cellpadding="0" cellspacing="0">
                  <tr>
                    <td><select name="p.pensyarah" size="1">
                      <option selected="selected">All Data</option>
                      <option value="p.pensyarah">Pensyarah</option>
                      <option value="program">Program</option>
                    </select>
                      <input type="text" name="search" id="search" size="25" />
                      <input class="firmBTN" type="submit" value="Search" /></td>
                  </tr>
                </table></td>
              </tr>
            </table>
        </form>

thank you again guido2004, you’re so very kind :slight_smile: