Home / Questions / 309973
Accepted
Martin Fernandez
Asked: 2020-11-21 03:45:42 +0800 CST 2020-11-21 03:45:42 +0800 CST

Three Variant Conditional View [MySQL]

  • 772

Conditions

Income - sum

Expense - subtraction

the operation (Input-Output) is made between the codes that are from the same place.

Table where I extract the information to create the view

enter image description here

How I want the view to be

enter image description here

how is it

enter image description here

Code

View:

select 

/*Columna codigo*/
desglose_flujo_bobinas.Codigo AS Codigo,

/*Columna Cantidad en Lugar 1*/
IFNULL(((SELECT SUM(Peso) FROM desglose_flujo_bobinas where TIPO = 'Ingreso' AND ubicacion = 'Lugar 1') -
             (SELECT SUM(Peso) FROM desglose_flujo_bobinas where TIPO = 'Egreso' AND ubicacion = 'Lugar 1')),
(SELECT SUM(Peso) FROM desglose_flujo_bobinas where TIPO = 'Ingreso' AND ubicacion = 'Lugar 1')) as 'Lugar 1',

/*Columna cantidad en Lugar 2*/
IFNULL(((select SUM(Peso) from desglose_flujo_bobinas where (Tipo = 'Ingreso' AND ubicacion = 'Lugar 2')) -
             (select SUM(Peso) from desglose_flujo_bobinas where (Tipo = 'Egreso' AND ubicacion = 'Lugar 2'))),
(select SUM(Peso) from desglose_flujo_bobinas where (Tipo = 'Ingreso' AND ubicacion = 'Lugar 2'))) AS 'Lugar 2' 

from sige_gest_materiaprima.desglose_flujo_bobinas 

Group by Codigo

try with

Validate what code was being worked with but being the same table I fall into a redundancy by making this condition inside the where (Code = Code).

->Returns exactly the same table

I thought about taking the id of each item as a reference but I can't think of how this can help me.

FIRST UPDATE

I tried concatenating the code data with the location, leaving a code similar to this, BOB-1Lugar1I made a column with the name of codigoProductowith that information and introduced it in the where with this condition ((SELECT CONCAT(Codigo, ubicacion) AS concatenado) = codigoProducto), this gives me this result, it should be noted that to get the view that I get below as well add to GROUP BYthe product code column

enter image description here

This is more like what I need.

SECOND UPDATE (Problem solved)

The resolution is explained in the answer to the question.

Caveat

The tables that I use and the code that I show is an adaptation of what I really have, in fact the photos are taken in an excel table. Adapt everything for a better understanding and not be confused by data, columns or non-relevant code.

mysql

1 Answers

  1. Best Answer

    To solve the problem I started looking for a new index that has to do with the code and in turn with the type, this problem was solved in the first update as I show it in the image of the table.

    Now a new problem arose and it was that although the subtraction operations were done correctly, the code was not grouped in the same row.

    Although the result was the same, I was not completely convinced by the idea of ​​duplicating data and I found the function GROUP_CONCATthat what it does is gather the data in the same cell of the columns that are indicated in this way, group_concat(col_1,col_2,...)in turn I group all the select echo to the codes so that all the ones found are shown ( GROUP BY codigo).

    To conclude and make the view neat, I assign the data type that must correctly contain the concatenated columns using unsignedsince if I let it assign one by default it shows it as STRING.

    End code:

    Select 
         tabla_datos_encontrados.codigoNo AS codigo, 
         CAST(GROUP_CONCAT(tabla_datos_encontrados.`Lugar 1` SEPARATOR ',') AS UNSIGNED)AS `Lugar 1`,
         CAST(GROUP_CONCAT(tabla_datos_encontrados.`Lugar 2` SEPARATOR ',') AS UNSIGNED) AS `Lugar 2`
        FROM
            (select 
            /*Columna codigo*/
            desglose_flujo_bobinas.bob_id as Flujo,
            (SELECT CONCAT(desglose_flujo_bobinas.codigo, desglose_flujo_bobinas.ubicacion_actual) AS codigoProd) AS codigoProducto,
            desglose_flujo_bobinas.Codigo AS Codigo
            /*Columna Cantidad en Lugar 1*/
            IFNULL(((SELECT SUM(Peso) FROM desglose_flujo_bobinas where TIPO = 'Ingreso' AND ubicacion = 'Lugar 1') -
                         (SELECT SUM(Peso) FROM desglose_flujo_bobinas where TIPO = 'Egreso' AND ubicacion = 'Lugar 1')),
            (SELECT SUM(Peso) FROM desglose_flujo_bobinas where TIPO = 'Ingreso' AND ubicacion = 'Lugar 1')) as 'Lugar 1',
            /*Columna cantidad en Lugar 2*/
            IFNULL(((select SUM(Peso) from desglose_flujo_bobinas where (Tipo = 'Ingreso' AND ubicacion = 'Lugar 2')) -
                         (select SUM(Peso) from desglose_flujo_bobinas where (Tipo = 'Egreso' AND ubicacion = 'Lugar 2'))),
            (select SUM(Peso) from desglose_flujo_bobinas where (Tipo = 'Ingreso' AND ubicacion = 'Lugar 2'))) AS 'Lugar 2' 
            from sige_gest_materiaprima.desglose_flujo_bobinas 
        Group by Codigo, codigoProducto) as tablaDatosEncontrados
Group By Codigo

    RETURNED VIEW

    • 0