Adding total values from multiple table in MySQL with php

Question

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;
}

Professor Abronsius · Accepted Answer · 2015-09-21 08:16:15Z

2

I hope the following is what you are after:

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

edited Sep 21, 2015 at 8:16

answered Sep 21, 2015 at 7:49

Professor Abronsius

33.9k5 gold badges36 silver badges49 bronze badges

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

1 Comment

Rookie Recruits Over a year ago

Thanks! I tested this as a second option and it works. 2 good ways to do this.

Sunil Pachlangia · Accepted Answer · 2015-09-21 07:29:49Z

1

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

answered Sep 21, 2015 at 7:29

Sunil Pachlangia

2,0613 gold badges17 silver badges26 bronze badges

6 Comments

Rookie Recruits Over a year ago

I tried it but no luck. It does not display. Just disappears.

Sunil Pachlangia Over a year ago

@RookieRecruits Can you post screen shot of your tabels

Rookie Recruits Over a year ago

I cannot, doesn't seem to let me.

Martyn Shutt Over a year ago

@Rookie Recruits you need to replace column in the subqueries with the names of your columns in your tables you want to count, then you want to sum them in your main query by referring to whatever you named the columns in your subquery.

Martyn Shutt Over a year ago

@Rookie Recruits Then change column to price in the subqueries, and column to value in your top query to sum your calculated columns.

|

Collectives™ on Stack Overflow

Adding total values from multiple table in MySQL with php

2 Answers 2

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related