Fix in MySQL

Question

Fix in MySQL

Navigation

#1 by (1 votes)

1

I have two related tables, movement and details_movement :

movimiento
+---------------------+-----------+------+-----+-------------------+-------+
| Field               | Type      | Null | Key | Default           | Extra |
+---------------------+-----------+------+-----+-------------------+-------+
| codigo_movimiento   | int(10)   | NO   | PRI | NULL              |       |
| tipo_movimiento     | char(15)  | NO   |     | NULL              |       |
| tipo                | char(15)  | NO   |     | NULL              |       |
| id_proveedor        | int(10)   | YES  |     | NULL              |       |
| codigo_departamento | int(10)   | YES  |     | NULL              |       |
| fecha_movimiento    | timestamp | NO   |     | CURRENT_TIMESTAMP |       |
| id_usuario          | int(10)   | NO   | MUL | NULL              |       |
| status_movimiento   | char(15)  | NO   |     | NULL              |       |
+---------------------+-----------+------+-----+-------------------+-------+

detalles_movimiento
+---------------------+----------+------+-----+---------+-------+
| Field               | Type     | Null | Key | Default | Extra |
+---------------------+----------+------+-----+---------+-------+
| codigo_movimiento   | int(10)  | NO   | MUL | NULL    |       |
| codigo_insumo       | int(10)  | NO   | MUL | NULL    |       |
| cantidad_movimiento | int(10)  | NO   |     | NULL    |       |
| status_movimiento   | char(15) | NO   |     | NULL    |       |
+---------------------+----------+------+-----+---------+-------+

I have to obtain for a report the input that had the most outputs in a certain time.

To solve it I used this query:

SELECT codigo_insumo, cantidad_movimiento FROM movimiento JOIN detalle_movimiento on movimiento.codigo_movimiento=detalle_movimiento.codigo_movimiento WHERE movimiento.fecha_movimiento BETWEEN '2016-11-01' AND '2016-11-18 23:59:59' AND tipo_movimiento='SALIDA';

... and throws me these results:

+---------------+---------------------+
| codigo_insumo | cantidad_movimiento |
+---------------+---------------------+
|             2 |                   3 |
|             4 |                   5 |
|             3 |                   2 |
|             4 |                   4 |
|             2 |                   5 |
|             3 |                   5 |
|             4 |                   4 |
|             3 |                   7 |
|             2 |                   2 |
+---------------+---------------------+

Now I have tried to transform that arrangement into a new arrangement where I add up all the movements of the same input, so I get the inputs with more outputs.

I've tried a lot of things and then I do not know, I'd like to know if there's a better way to get what I want or if I'm doing well, like doing the fix.

mysql

asked by Elias Tutungi 19.11.2016 в 17:36

source

1 answer

Display query data in a toast Python / Django warn that your session is going to expire

score 1 · Accepted Answer

SELECT 
    codigo_insumo, 
    SUM(cantidad_movimiento) 
FROM 
    movimiento 
    JOIN detalle_movimiento 
        on movimiento.codigo_movimiento=detalle_movimiento.codigo_movimiento 
WHERE 
    movimiento.fecha_movimiento BETWEEN '2016-11-01' AND '2016-11-18 23:59:59' 
    AND tipo_movimiento='SALIDA'
GROUP BY
    codigo_insumo;

If you are only interested in the oldest one, you have to add

ORDER BY SUM(cantidad_movimento)
LIMIT 1;