Get a total through a single query

Question

Get a total through a single query

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

5

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'");
      if($res_reserva=$result_reserva->fetch_assoc())
      {
        $reserva="SI";
        $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;
      }
      else{
        $reserva="NO";
      }

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!

mysql sql php

asked by MoteCL 02.10.2018 в 21:30

source

3 answers

Parameter in directive * ngFor Angular 6 Problem consult self-referenced table

score 0 · Answer 1

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:

$cantidad=0;
$reserva=0;

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

$sql="SELECT cantidad FROM Inv_Reserva where CodPza='".$codpza."'";
$resultado = $mysqli->query($sql);
if($resultado->num_rows > 0){
    while($row = $resultado->fetch_assoc()) {
       $reserva=$row["cantidad"];
    }
}
$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.

score 0 · Answer 2

TRY WITH

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

score 0 · Answer 3

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