CODEWITHSUNDEEP
phpmysql
Haika
Haika

Reputation: 39

How to SUM, SUM of Total from three table

Sorry I would like to repeat the question since I think was not clear to you, Im doing this b'cose is difficult to me to get what I expect.

I want to get SUM of Total which i get from three table from single query, Here my tables!!

1: payment

id | school_fee | trans_fee
1    20000        3000
2    10000        2000

The total is 35000

and the codes is here

 <?php



             //mysql_connect


if (isset($_GET['query'])) 
          {    
                 $query=$_GET['query'];

      // Instructions if $_POST['value'] exist    
      }  

                  // gets value sent over search form

                    $min_length = 3;
          // you can set minimum length of the query if you want

        if(strlen($query) >= $min_length){ // if query length is more or equal minimum 
                  length         then

    $query = htmlspecialchars($query); 
    // changes characters used in html to their equivalents, for example: < to &gt;

    $query = mysql_real_escape_string($query);
    // makes sure nobody uses SQL injection

    $raw_results = mysql_query("SELECT SUM(school_fee+trans_fee) As Total   
            FROM payment WHERE (`class` LIKE '%".$query."%')") or die(mysql_error());



               // * means that it selects all fields, you can also write: `id`,  
                   `title`, `text`
             // articles is the name of our table

            // '%$query%' is what we're looking for, % means anything, for example  
            if    
                         $query  
              is Hello
    // it will match "hello", "Hello man", "gogohello", if you want exact match use  
              `title`='$query'
    // or if you want to match just full word so "gogohello" is out use '% $query %'  
            ...OR ... '$query %' ... OR ... '% $query'


    if(mysql_num_rows($raw_results) > 0){

      // if one or more rows are returned do following

        while($results = mysql_fetch_array($raw_results)){
         while($results2 = mysql_fetch_array($raw_results2)){

        // 
       $results = mysql_fetch_array($raw_results) puts data from database into  
     array, while it's valid it does the loop


            // posts results gotten from database(title and text) you can also show    
            id ($results['id'])
        }{



            echo " &nbsp;Total amount of money payed by&nbsp;"       
        .$results['class'] ."&nbsp;"."class is&nbsp;" . $results ['Total'] .  
          "&nbsp;/=Tshs";



               echo"<br>";  echo"<br>"; 
           }



          }
            }
             }

     ?>

2:payment_one

     id | school_fee | trans_fee
     1    10000        20000
     2    30000        50000

     The Total is 110000

The CODEs is the same with the first one table except name of the table here the codes

 <?php



             //mysql_connect


if (isset($_GET['query'])) 
          {    
                 $query=$_GET['query'];

      // Instructions if $_POST['value'] exist    
      }  

                  // gets value sent over search form

                    $min_length = 3;
          // you can set minimum length of the query if you want

        if(strlen($query) >= $min_length){ // if query length is more or equal minimum 
                  length         then

    $query = htmlspecialchars($query); 
    // changes characters used in html to their equivalents, for example: < to &gt;

    $query = mysql_real_escape_string($query);
    // makes sure nobody uses SQL injection

    $raw_results = mysql_query("SELECT SUM(school_fee+trans_fee) As Total   
            FROM payment_one WHERE (`class` LIKE '%".$query."%')") or die(mysql_error());



               // * means that it selects all fields, you can also write: `id`,  
                   `title`, `text`
             // articles is the name of our table

            // '%$query%' is what we're looking for, % means anything, for example  
            if    
                         $query  
              is Hello
    // it will match "hello", "Hello man", "gogohello", if you want exact match use  
              `title`='$query'
    // or if you want to match just full word so "gogohello" is out use '% $query %'  
            ...OR ... '$query %' ... OR ... '% $query'


    if(mysql_num_rows($raw_results) > 0){

      // if one or more rows are returned do following

        while($results = mysql_fetch_array($raw_results)){
         while($results2 = mysql_fetch_array($raw_results2)){

        // 
       $results = mysql_fetch_array($raw_results) puts data from database into  
     array, while it's valid it does the loop


            // posts results gotten from database(title and text) you can also show    
            id ($results['id'])
        }{



            echo " &nbsp;Total amount of money payed by&nbsp;"       
        .$results['class'] ."&nbsp;"."class is&nbsp;" . $results ['Total'] .  
          "&nbsp;/=Tshs";



               echo"<br>";  echo"<br>"; 
           }



          }
            }
             }

     ?>

The last one is

3: payment_two

    id | school_fee | trans_fee
    1    10000        20000
    2    30000        10000

    The Total is 70000

Codes as follows

<?php



             //mysql_connect


if (isset($_GET['query'])) 
          {    
                 $query=$_GET['query'];

      // Instructions if $_POST['value'] exist    
      }  

                  // gets value sent over search form

                    $min_length = 3;
          // you can set minimum length of the query if you want

        if(strlen($query) >= $min_length){ // if query length is more or equal minimum 
                  length         then

    $query = htmlspecialchars($query); 
    // changes characters used in html to their equivalents, for example: < to &gt;

    $query = mysql_real_escape_string($query);
    // makes sure nobody uses SQL injection

    $raw_results = mysql_query("SELECT SUM(school_fee+trans_fee) As Total   
            FROM payment_two WHERE (`class` LIKE '%".$query."%')") or die(mysql_error());



               // * means that it selects all fields, you can also write: `id`,  
                   `title`, `text`
             // articles is the name of our table

            // '%$query%' is what we're looking for, % means anything, for example  
            if    
                         $query  
              is Hello
    // it will match "hello", "Hello man", "gogohello", if you want exact match use  
              `title`='$query'
    // or if you want to match just full word so "gogohello" is out use '% $query %'  
            ...OR ... '$query %' ... OR ... '% $query'


    if(mysql_num_rows($raw_results) > 0){

      // if one or more rows are returned do following

        while($results = mysql_fetch_array($raw_results)){
         while($results2 = mysql_fetch_array($raw_results2)){

        // 
       $results = mysql_fetch_array($raw_results) puts data from database into  
     array, while it's valid it does the loop


            // posts results gotten from database(title and text) you can also show    
            id ($results['id'])
        }{



            echo " &nbsp;Total amount of money payed by&nbsp;"       
        .$results['class'] ."&nbsp;"."class is&nbsp;" . $results ['Total'] .  
          "&nbsp;/=Tshs";



               echo"<br>";  echo"<br>"; 
           }



          }
            }
             }

     ?>

So I want to query by php so that I may get 215000 as Grand total of those three table, I need the help please. NOTE: id is auto increament.

Upvotes: 3

Views: 108

Answers (1)

Filipe Silva
Filipe Silva

Reputation: 21657

You could do that with UNION:

SELECT SUM(total) FROM 
(
  SELECT SUM(school_fee+trans_fee) As Total   
            FROM payment
  UNION 
  SELECT SUM(school_fee+trans_fee) As Total   
            FROM payment_one
  UNION
  SELECT SUM(school_fee+trans_fee) As Total   
            FROM payment_two
 ) a

sqlfiddle demo

Upvotes: 2

Related Questions