Sphiinx Sphiinx - 6 months ago 22
PHP Question

Sum of all the numbers in a column for 3 tables?

I'm trying to use PHP and SQL to echo the sum of columns from 3 separate tables. I'm wondering how I can simplify this as it's very bloated.

Thanks!

public function getTotalSum() {
include("dbconnect.php");
$query1 = "SELECT SUM(column1) AS 'count' FROM table1";
$query2 = "SELECT SUM(column2) AS 'count' FROM table2";
$query3 = "SELECT SUM(column3) AS 'count' FROM table3";
$result1 = mysqli_query($sqlcon, $query1);
$result2 = mysqli_query($sqlcon, $query2);
$result3 = mysqli_query($sqlcon, $query3);

if (mysqli_num_rows($result1) > 0 && mysqli_num_rows($result2) > 0 && mysqli_num_rows($result3) > 0) {
while ($row = $result1->fetch_assoc() + $result2->fetch_assoc() + $result3->fetch_assoc()) {
echo $row['count'];
}
}
}

Answer

Use one sql query instead of multiple ones.
Apply the following approach:

public function getTotalSum() {
    include("dbconnect.php");
    $query = "SELECT 
                (SELECT SUM(column1) AS 'count' FROM table1)
                + (SELECT SUM(column2) AS 'count' FROM table2)
                + (SELECT SUM(column3) AS 'count' FROM table3) AS count;    
    ";
    $result = mysqli_query($sqlcon, $query);

    if (mysqli_num_rows($result) > 0) {
        $row = $result->fetch_assoc();
        echo $row['count'];
    }
}