How to join and sum a column in two tables based on another column using mysqli and php (just using query)

Question

I have 2 tables about blood bank:

1. donates
2. orders

in donates table I have 2 fields showing how many donations we have:

------------------------
| blood_group | amount |
------------------------
|    A+       |   2    |
|    B-       |   3    |
|    O+       |   4    |
|    A+       |   3    |
|    O+       |   1    |

in orders table I have 2 column that how many requests we submit based on blood group:

------------------------
| blood_group | amount |
------------------------
|    A+       |   4    |
|    B-       |   3    |
|    O+       |   4    |
|    AB-      |   6    |

My problem is I want to use mysqli query to get an array that show me this result based on these conditions:

1. show how many we need group by blood_group
2. if we don't need any blood_group or we don't have any request for that blood type show zero (not showing null)
3. not showing negative number for our blood shortage

I manage to do this so far:

<?php
$con = mysqli_connect("localhost", "root", "", "test");
// Check connection
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql ="SELECT donates.blood_group as blood_group,
                donates.amount as donates_amount,
                orders.amount as orders_amount,
                                    FROM `donates`
                                    LEFT JOIN `orders` 
                                    ON donates.blood_group = orders.blood_group
                                    GROUP BY donates.blood_group";
// Perform queries
$result = mysqli_query($con, $sql);

if (!$result = mysqli_query($con, $sql)) {
    echo "SQLSTATE error: " . mysqli_sqlstate($con);
    echo "<br>";
    echo "SQLSTATE error: " . mysqli_error($con);
    exit;
}

$result = mysqli_fetch_all($result, MYSQLI_ASSOC);

var_dump($result);

mysqli_close($con);

That query shows me sum of blood_groups but here is the main question:

So here are the main questions:

1. how to subtract (donates_amount and orders_amount)
2. how to make them positive (subtract which one first)
3. how to show the result even if one blood group is not presented on the other (full join)

Answer 1

Use union all and group by:

select blood_group, sum(donate_amount) as donate_amount,
       sum(order_amount) as order_amount
from ((select blood_group, amount as donate_amount, 0 as order_amount
       from donates
      ) union all
      (select blood_group, 0 as donate_amount, amount as order_amount
       from orders
      )
     ) od
group by blood_group;

The only caveat is that a blood group needs to be in one of the tables. If you have a separate table of all of them, you should use that. For instance:

select bg.*,
       coalesce(donate_amount, 0) as donate_amount,
       coalesce(order_amount, 0) as order_amount
from blood_groups bg left join
     (select blood_group, sum(amount) as donate_amount
      from donates
      group by blood_group
     ) d
     on d.blood_group = bg.blood_group left join
     (select blood_group, sum(amount) as order_amount
      from donates
      group by blood_group
     ) o
     on o.blood_group = bg.blood_group ;

In either of these queries, you can get the difference using - and show negative numbers as 0 using greatest(). For instance:

greatest(sum(donate_amount) - sum(order_amount), 0)

Answer 2

To answer your first question :

how to subtract (donates_amount and orders_amount)

You must use SUM() function with a minus sign:

SUM(donates.amount - orders.amount);

this will subtract the total sum of two tables

But we have some problem here: you may have null values (because you may not have some of the blood groups present in one of tables) that give the wrong result. you must change the null values to zero with COALESCE() function:

SUM(COALESCE(donates.amount,0) - COALESCE(orders.amount,0))

We must extra check if the result does not equal to null:

COALESCE(SUM(COALESCE(donates.amount,0) - COALESCE(orders.amount,0)),0)

how to make them positive (subtract which one first)

And at last if you want to avoid negative numbers you must use mysqli math functions named ABS() that give you absulute value:

ABS(COALESCE(SUM(COALESCE(donates.amount,0) - COALESCE(orders.amount,0)),0))

so your query will look like this:

$sql = "SELECT donates.blood_group as blood_group,
          COALESCE(donates.amount,0) as donates_amount,
          COALESCE(orders.amount,0) as orders_amount,
          ABS(COALESCE(SUM(COALESCE(donates.amount,0) - COALESCE(orders.amount,0)),0)) as needed_amount
                                    FROM `donates`
                                    LEFT JOIN `orders` 
                                    ON donates.blood_group = orders.blood_group
                                    GROUP BY donates.blood_group";

how to show the result even if one blood group is not presented on the other (full join)

In order to make full join you must use union with the invers form of your query. so that you find other records in orders table and unite the results into one results:

$sql = "SELECT donates.blood_group as blood_group,
          COALESCE(donates.amount,0) as donates_amount,
          COALESCE(orders.amount,0) as orders_amount,
          ABS(COALESCE(SUM(COALESCE(donates.amount,0) - COALESCE(orders.amount,0)),0)) as needed_amount
              FROM `donates`
              LEFT JOIN `orders` 
              ON donates.blood_group = orders.blood_group
              GROUP BY donates.blood_group 
              UNION
         SELECT orders.blood_group as blood_group,
            COALESCE(donates.amount,0) as donates_amount,
            COALESCE(orders.amount,0) as orders_amount,
            ABS(COALESCE(SUM(COALESCE(orders.amount,0) - COALESCE(donates.amount,0)),0)) as needed_amount
               FROM `orders`
               LEFT JOIN `donates`
               ON orders.blood_group = donates.blood_group
               GROUP BY orders.blood_group";