CODEWITHSUNDEEP
phpmysql
Rookie Recruits
Rookie Recruits

Reputation: 93

Adding total values from multiple table in MySQL with php

I have 2 tables, I need to sum up the total value from each tables price column. I did some research on Stack but the solutions I saw were too long and did not seems efficient enough code. With MySQL I am trying to get the total value from StoreCoins plus the total value from StoreMemorabilia and the total value from supplies then I want the sum of the total of both table's column. This first code is currently not working...

if($results = $this->dbConn->query("SELECT SUM(column) AS InventoryValue FROM (
                                        SELECT SUM(column) AS value FROM StoreCoins
                                        UNION ALL
                                        SELECT SUM(column) AS value FROM StoreMemorabilia
                                        ) allposts")){
while($data = $results->fetch_assoc()){
    $totalVal = $data['InventoryValue'];
    $totalVal .= ".00";

I need to know what I am doing wrong in the code above.

On another note (this is php related): I have the following script that once the correct value is returned it decides where to put the commas for dollar values. I would like a recommendation to a much more efficient way to create this function and its results. The switch statement works just fine and does what I need it to do, I just want a more eloquent way of writing this.

switch(strlen($Val)){
    case 7:
        $rem = substr($Val, 1, 6);
        $Val = substr_replace($Val, ",", 1);
        $Val = $Val.$rem;
        break;
    case 8:
        $rem = substr($Val, 2, 6);
        $Val = substr_replace($Val, ",", 2);
        $Val = $Val.$rem;
        break;
    case 9:
        $rem = substr($Val, 3, 6);
        $Val = substr_replace($Val, ",", 3);
        $Val = $totalVal.$rem;
        break;
    case 10:
        $rem = substr($Val, 1, 10);
        $Val = substr_replace($Val, ",", 1);
        $Val = $Val.$rem;
        $rema = substr($Val, 5, 6);
        $Val = substr_replace($Val, ",", 5);
        $Val = $Val.$rema;
}

Upvotes: 1

Views: 1210

Answers (2)

Professor Abronsius
Professor Abronsius

Reputation: 33813

I hope the following is what you are after:

select (
    ( select sum(`price`) from `StoreCoins` ) 
        + 
    ( select sum(`price`) from `StoreMemorabilia` )
) as 'total';

Upvotes: 2

Sunil Pachlangia
Sunil Pachlangia

Reputation: 2071

Change the query like this

SELECT SUM(column) FROM ( 
SELECT SUM(column) AS value FROM StoreCoins

UNION 

SELECT SUM(column) AS value FROM StoreMemorabilia
) InventoryValue

Upvotes: 1

Related Questions