MySql/PHP SUM 2 tables 1 query

Question

I have been having the most difficult time ever with this problem. I have 2 tables with total columns that I want to SUM together. They both have the same columns, I am using two tables as one is a script generated table of data and the other is user entered data and we need them separate. Except now we need to SUM(total) them together.

Table 1

   +-----------+-----+--------+------+
   | date      |t_id | t_port | total|
   +-----------+-----+--------+------+
   |2012-04-01 | 1271| 101    | 80.00|
   +-----------+-----+--------+------+

Table 2

+----------+------+--------+-------+
| date     | t_id | t_port | total |
+----------+------+--------+-------+
|2012-04-20| 1271 | 101    | 120.00|
+----------+------+--------+-------+

Total should be $200.00

HERE IS MY QUERY

"SELECT SUM(cntTotal) as total FROM CBS_WO WHERE (date BETWEEN '$monthSecond' AND '$monthEnd') AND t_port = '$t_port' AND t_id = '$t_id' UNION SELECT SUM(cntTotal) as total FROM CNT_MODS WHERE (date BETWEEN '$monthSecond' AND '$monthEnd') AND t_port = '$t_port' AND t_id = '$t_id'"

This query seems to work in phpMyAdmin as I get 2 rows. (1 for each table), logically I used a WHILE loop in PHP to add the two rows together. After echo'ing out each row manually I discovered my second row isn't showing up in the loop, yet it does in the query?

Can't figure out why this is happening, I am certain it's something silly but I been at this code for over 16hrs already and need a new set of eyes.

PHP CODE

function periodTotal()
{
    include('/sql.login.php');  

    $t_id                   = "1271";
    $t_port                 = "101";
    $date                   = date("Y-m-d");

    # FIND MONTH (DATE)
    $monthStart     = date("Y-m-d", strtotime(date('m').'/01/'.date('Y').' 00:00:00')); 
    $monthFirst     = date("Y-m-d", strtotime('-1 second',strtotime('+15 days',strtotime(date('m').'/01/'.date('Y').' 00:00:00'))));
    $monthSecond    = date("Y-m-d", strtotime('-1 second',strtotime('+16 days',strtotime(date('m').'/01/'.date('Y').' 00:00:00'))));
    $monthEnd       = date("Y-m-d", strtotime('-1 second',strtotime('+1 month',strtotime(date('m').'/01/'.date('Y').' 00:00:00'))));

    if ($date = $monthFirst) 
    {
        $sql = $dbh->prepare("SELECT SUM(cntTotal) as total FROM CBS_WO WHERE (date BETWEEN '$monthStart' AND '$monthFirst') AND t_port = '$t_port' AND t_id = '$t_id' UNION SELECT SUM(cntTotal) as total FROM CNT_MODS WHERE (date BETWEEN '$monthStart' AND '$monthFirst') AND t_port = '$t_port' AND t_id = '$t_id'");
        $sql->execute();
    } 
    else 
    {
        $sql = $dbh->prepare("SELECT SUM(cntTotal) as total FROM CBS_WO WHERE (date BETWEEN '$monthSecond' AND '$monthEnd') AND t_port = '$t_port' AND t_id = '$t_id' UNION SELECT SUM(cntTotal) as total FROM CNT_MODS WHERE (date BETWEEN '$monthSecond' AND '$monthEnd') AND t_port = '$t_port' AND t_id = '$t_id'");
        $sql->execute();
    }
        while($row = $sql->fetch(PDO::FETCH_ASSOC))
        {
            $total += $row['total'];
        }

    return $total;
}

Halcyon · Accepted Answer · 2012-04-21 18:17:24Z

2

Does this work for you?

SELECT SUM(`total`) as `total` FROM ((
    SELECT SUM(cntTotal) as total FROM CBS_WO
    WHERE (date BETWEEN '$monthSecond' AND '$monthEnd') AND t_port = '$t_port' AND t_id = '$t_id'
) UNION (
    SELECT SUM(cntTotal) as total FROM CNT_MODS
    WHERE (date BETWEEN '$monthSecond' AND '$monthEnd') AND t_port = '$t_port' AND t_id = '$t_id'"
)) as temp

This might be more efficient:

SELECT SUM(total) FROM (
    SELECT cntTotal FROM CBS_WO
    WHERE (date BETWEEN '$monthSecond' AND '$monthEnd') AND t_port = '$t_port' AND t_id = '$t_id'
) UNION (
    SELECT cntTotal FROM CNT_MODS
    WHERE (date BETWEEN '$monthSecond' AND '$monthEnd') AND t_port = '$t_port' AND t_id = '$t_id'"
) as temp

(only has one SUM) but you'd have to test it.

answered Apr 21, 2012 at 18:17

Halcyon

57.7k10 gold badges93 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

devfunkd Over a year ago

Yes, it works perfectly in sql but I can't seem to be able to display it in PHP?

devfunkd Over a year ago

$row = $sql->fetch(PDO::FETCH_ASSOC); $total = $row['total'];

H2O · Accepted Answer · 2012-04-21 19:11:56Z

0

It seems to be able to display your it in PHP......

=>   set the PDO error mode to exception

     $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $dbh->prepare(Run Sql Query here...........);

=>   exceute the query 

stmt->execute();

=>by setting the FETCH mode we can set

$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);

=> The iterator will fetch the results for us.

 foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) 
        { 
        echo $v; 
        }

answered Apr 21, 2012 at 19:11

H2O

396 bronze badges

2 Comments

devfunkd Over a year ago

Ok, I think I found the issue but not sure how to solve it. I did as you said above and the query error I got was "Every derived table must have its own alias" -- isn't the AS temp at the end of the query the alias?

devfunkd Over a year ago

GOT IT! I used the first query by Frits van Campen's answer and it worked perfectly, for some reason the second one was causing the issue. THANK YOU! for all the help and answers.

Collectives™ on Stack Overflow

MySql/PHP SUM 2 tables 1 query

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related