Get a total through a single query


I have the following tables where I'm taking an inventory, but I also have a reservation table.

 Tabla Inventario
| pieza  |cantidad |
| pieza1 |100      |
| pieza2 |20       |
| pieza3 |30       |
| pieza4 |10       |
| pieza5 |30       |

 Tabla Reserva
| pieza  |cantidad |
| pieza1 |10       |
| pieza1 |20       |
| pieza2 |10       |
| pieza2 |5        |
| pieza5 |30       |

I have the following query where I make a query if the piece has a reservation.

 $result_reserva = $conexion->query("SELECT * FROM Inv_Reserva
      where CodPza='$codpza'");
        $query   = "SELECT SUM(Cantidad) FROM Inv_Reserva where CodPza='$codpza' ";
        $result  = mysqli_query($conexion, $query);
        $row     = mysqli_fetch_row($result);
        $reservatotal = $row[0];

        $query2       = "SELECT Cantidad FROM Inv_Inventario where CodPza='$codpza'";
        $result2      = mysqli_query($conexion, $query2);
        $roww         = mysqli_fetch_row($result2);
        $invTotal = $roww[0];

        $total = $reservaTotal-$invTotal;

If you find me I would like to make a query where I get the visible inventory available (Cantidad Inventario - Cantidad Reserva) .

I have the following query to get the total sum of the reservations.

//Query Reserva total
SELECT SUM(Cantidad) FROM 'Inv_Reserva' WHERE CodPza='$pieza' ;
 //Query Inventario 
SELECT Cantidad FROM Inv_Inventario WHERE CodPza='$pieza';

But I would like to be able to do the addition and subtraction in a single query. I hope you have explained me well. Greetings!

asked by MoteCL 02.10.2018 в 21:30

3 answers


And why do not you calculate it directly with php? You make a query for the total value, another for the reservation and from php a new variable with the subtraction:


$sql="SELECT cantidad FROM Inv_Inventario where CodPza='".$codpza."'";
$resultado = $mysqli->query($sql);
if($resultado->num_rows > 0){
    while($row = $resultado->fetch_assoc()) {

$sql="SELECT cantidad FROM Inv_Reserva where CodPza='".$codpza."'";
$resultado = $mysqli->query($sql);
if($resultado->num_rows > 0){
    while($row = $resultado->fetch_assoc()) {
$total = $cantidad - $reserva;

With this you would remove one of the variables, repeat the process for the other and then create a new variable and do the subtraction.

I think you understand. If not, you tell me.

answered by 02.10.2018 в 23:05


select pieza, 
       sum(cantidad - cantidad_reserva) resta,
       sum(cantidad + cantidad_reserva) suma
from ( ---calculas la suma de reservas por pieza
select A.cantidad,
      (select NVL(sum(cantidad),0) from Inv_Reserva where pieza =A.pieza) AS cantidad_reserva
from Inv_Inventario A)
group by pieza
answered by 03.10.2018 в 00:03

You could try to make a join of the two tables since the codPieza is the same in both. Something like this:

 select sum(i.cantidad) - r.cantidad as total from inventario i join reservas r on i.codPieza = r.codPieza

When we do a join we have all the records of the two tables, in this case you want to subtract their values, but in a query you have to unite the value of the two tables in a query, and once you have value then subtract it

pd: the "total ace" you can change it, in sql you can use "as" to change the name (only visually) of the column, for example you could put as follows I hope it serves you

answered by 09.10.2018 в 14:31