calculo de diferentes gupos en una misma consulta

[juan_aalberto]

hola el problema es el siguiente tengo dos tablas y una consulta como se muestra a continuación:

la tabla matrizes
id________________banco______________direccion____ _____nombre_matriz
1_________________bancox__________calle pinoz___________matriz1
2__________________bancoy____________calle santiago_________matriz2
3__________________bancoz____________calle rodeo____________matriz3

la tabla opmatrizes
id_____matrize_id_____depositos______retiros
1________1___________42353_________604
2_________3__________66____________67
3_________2___________4_____________18
4_________3___________14____________28

la consulta es la siguiente:

Código MySQL:

SELECT fecha, matrize_id, banco, no_cuenta, cheque, folio, factura, cuenta_a_cuenta, mercado_nacional, anticipos_eastmann, factura_fiscal, factura_digital, gastos_generales, nombresucursal, sucursal, cab, kilos, lote, concepto, depositos, retiros, (@total:=@total+depositos-retiros) balance, beneficiario
FROM opmatrizes JOIN (SELECT @total:=0) t1 order by matrize_id;
 

dicha consulta me genera lo siguiente:
id_____matrize_id_____depositos______retiros_____b alance
1________1___________42353_________604______41749
2_________2__________4______________18_______41735
3_________3___________66____________67______41734
4_________3___________14____________28______41720

pero lo que yo quiero que me muestre es esto:
id_____matrize_id_____depositos______retiros_____b alance
1________1___________42353_________604______41749
2_________2__________4______________18_______-14
3_________3___________66____________67______-1
4_________3___________14____________28______-15

Espero que alguien me pueda ayudar a formular una consulta mysql para que genere dicha tabla
se los agradeseria mucho ya que si no termino esta parte no podre continuar con lo demas

[vimon]

Buenos dias,

Si no quieres agrupar las matrices en unn solo bloque la consulta seria mas sencilla

Código MySQL:

select matrize_id, ...., (depositos-retiros) as balance 
from ....... 
where matrize_id=id_banco
 

Creo que no hace falta nada mas. Si quieres agrupar por bancos ya es otra cosa

Código MySQL:

select matrize_id, ....,sum (depositos-retiros) as balance 
from ....... 
where matrize_id=id_banco group by banco_id.
 

Con lo que pones no se decirte mas,

Eso si deberias poner los nombres de campos un poco mas explicito, banco_id, banco_nombre, ......movimientos_id, movimientos_banco (matrize_id), movimientos_deposito, .....

saludos

[juan_aalberto]

Hola gracias por la respuesta e metido la siguiente sentencia:

Código SQL:

SELECT matrize_id, depositos, retiros, (@total:=@total+depositos-retiros) balance
FROM opmatrizes JOIN (SELECT @total:=0) t1 WHERE `matrize_id` = `banco`;
 

y me arrojo lo siguiente:


matrize_id_____depositos______retiros_____balance
1___________42353_________604______41749

y yo lo que quiero es mostrar el balance de cada uno de los matrize_id en una sola consulta, entonces si pongo esta consulta:

Código MySQL:

SELECT matrize_id, banco, depositos, retiros, (@total:=@total+depositos-retiros) balance, beneficiario
FROM opmatrizes JOIN (SELECT @total:=0) t1 where matrize_id = 1;
 

me genera esto:
matrize_id_____depositos______retiros_____balance
___1___________42353_________604______41749

y si pongo este codigo:

Código MySQL:

SELECT matrize_id, banco, depositos, retiros, (@total:=@total+depositos-retiros) balance, beneficiario
FROM opmatrizes JOIN (SELECT @total:=0) t1 where matrize_id = 3;
 

me genera esto:
matrize_id_____depositos______retiros_____balance
___3___________66___________67_________-1
___3___________14___________28_________-15

por ultimo lo que se supone que tengo que hacer para obtener mi resultado deseado es esto:

Código MySQL:

SELECT matrize_id, banco, depositos, retiros, (@total:=@total+depositos-retiros) balance
FROM opmatrizes JOIN (SELECT @total:=0) t1 where matrize_id = 1
union
SELECT matrize_id, banco, depositos, retiros, (@total:=@total+depositos-retiros) balance
FROM opmatrizes JOIN (SELECT @total:=0) t1 where matrize_id = 3;
 

pero me muestra un resultado erroneo porque no me calcula el balance por cada matrize_id, lo que pasa es que me calcula el balance por cada registro:

matrize_id_____depositos______retiros_____balance
___1___________42353_________604______41749
___3___________66___________67_________41748
___3___________14___________28_________41734



y el resultado que yo quiero es este:
matrize_id_____depositos______retiros_____balance
___1___________42353_________604______41749
___3___________66___________67_________-1
___3___________14___________28_________-15

espero me puedas ayudar de antemano gracias

[vimon]

Hola buenos dias, me mire un poco mas el tema, y no ses si entiendo lo que quieres, pero en esta segunda lectura de tu peticion veo que uinicamente quieres que por cada matrize_id reste dos valores de campos,

yo pensaba que querías hacer un calculo de cada grupo matrize_id.

A ver si lo entiendo tu quieres que te enseñe todos los movimientos y su balance pero en cada matrize_id

Yo veo algo mas cómodo hacerlo con php, esto esta implementado en php o lo haces en la consola?

Saludos

[juan_aalberto]

lo hago en la consola, pero de hecho estaba viendo y si creo que es mas fácil con php, pero si lo pudiera hacer con mysql me seria de mas ayuda

[gnzsoloyo]

pero lo que yo quiero que me muestre es esto:
id_____matrize_id_____depositos______retiros_____b alance
1________1___________42353_________604______41749
2_________2__________4______________18_______-14
3_________3___________66____________67______-1
4_________3___________14____________28______-15

Perdona, pero si esto es lo que necesitas, te estás complicando innecesariamente:

Código MySQL:

mysql> SELECT
    ->     `matrices_id`,
    ->     `depositos`,
    ->     `retiros`,
    ->     SUM(`depositos`- `retiros`) balance
    -> FROM `test`.`op_martices`
    -> GROUP BY `matrices_id`;
+-------------+-----------+---------+---------+
| matrices_id | depositos | retiros | balance |
+-------------+-----------+---------+---------+
|           1 |     42353 |     604 |   41749 |
|           2 |         4 |      18 |     -14 |
|           3 |        66 |      67 |     -15 |
+-------------+-----------+---------+---------+
3 rows in set (0.00 sec)
 

Las variables de usuario no son necesarias nada más que para obtener una acumulación progresiva, que no es lo que estás buscando.
Ahora bien, si lo que quieres es que sume secuencialmente, mostrando el resumen de subtotales, no es buena idea usar las variables, porque deberías escribirlas con condicionales y necesitarías una o dos más como flags.
Es algo demasiado complicado para iniciarte en este tema.
Lo que sí puedes hacer es usar una clausula adicional:

Código MySQL:

SELECT 
    CAST(
        IF( depositos is null 
            and retiros is null 
            AND matrices_id is null,
            'Total', 
            IF(
                depositos is null 
                and retiros is null, 'Subtotal', matrices_id)) AS CHAR)`matrices_id`,
    CAST(IFNULL(`depositos`, '') AS CHAR) depositos,
    CAST(IFNULL(`retiros`, '') AS CHAR) retiros, 
    `balance`
FROM 
    (SELECT
        `matrices_id`,
        `depositos`,
        `retiros`, 
        SUM(`depositos`- `retiros`) balance
    FROM `test`.`op_martices`
    GROUP BY `matrices_id`, `depositos`,`retiros` WITH ROLLUP) T1
WHERE (depositos IS NULL AND retiros IS NULL) 
    OR (depositos IS NOT NULL AND retiros IS NOT NULL)
 

Esto daría:

Código MySQL:

+-------------+-----------+---------+---------+
| matrices_id | depositos | retiros | balance |
+-------------+-----------+---------+---------+
| 1           | 42353     | 604     |   41749 |
| Subtotal    |           |         |   41749 |
| 2           | 4         | 18      |     -14 |
| Subtotal    |           |         |     -14 |
| 3           | 14        | 28      |     -14 |
| 3           | 66        | 67      |      -1 |
| Subtotal    |           |         |     -15 |
| Total       |           |         |   41720 |
+-------------+-----------+---------+---------+
8 rows in set (0.02 sec)
 

Como verás, no es una solución para nada sencilla, pero es posible.

[juan_aalberto]

hola gnzsoloyo gracias por tu ayuda es exactamente lo que quiero hacer solo que en el campo balance quisiera que me pusiera los valores de subtotal
puse el código cambiándole una que otra cosa para que mysql me lo aceptara

Código MySQL:

SELECT 
* * CAST(
* * * * IF( depositos IS NULL 
* * * * * * AND retiros IS NULL 
* * * * * * AND matrize_id IS NULL,
* * * * * * 'Total', 
* * * * * * IF(
* * * * * * * * depositos IS NULL 
* * * * * * * * AND retiros IS NULL, 'Subtotal', matrize_id)) AS CHAR)`matrize_id`,
* * CAST(IFNULL(`depositos`, '') AS CHAR) depositos,
* * CAST(IFNULL(`retiros`, '') AS CHAR) retiros, 
* * `balance`
FROM 
* * (SELECT
* * * * `matrize_id`,
* * * * `depositos`,
* * * * `retiros`, 
* * * * SUM(`depositos`- `retiros`) balance
* * FROM `test`.`op_matrizes`
* * GROUP BY `matrize_id`, `depositos`,`retiros` WITH ROLLUP) T1
WHERE (depositos IS NULL AND retiros IS NULL) 
* * OR (depositos IS NOT NULL AND retiros IS NOT NULL)
 

pero me marco este error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CAST( IF( depositos IS NULL AND retiros IS NULL ' at line 2

Perdón por ser tan preguntón je pero es que la verdad apenas me estoy iniciando en mysql y hay muchas cosas que aun no se, espero me puedas ayudar a solucionar este error y también quisiera saber si puedes ser tan amable de explicarme un poquito como funciona el codigo

[juan_aalberto]

Código MySQL:

SELECT 
CAST(
IF( depositos IS NULL 
AND retiros IS NULL 
AND matrize_id IS NULL,
'Total', 
IF(
depositos IS NULL 
AND retiros IS NULL, 'Subtotal', matrize_id)) AS CHAR)`matrize_id`,
CAST(IFNULL(`depositos`, '') AS CHAR) depositos,
CAST(IFNULL(`retiros`, '') AS CHAR) retiros, `balance`
FROM 
(SELECT `matrize_id`, `depositos`, `retiros`, 
SUM(`depositos`- `retiros`) balance
FROM `test`.`op_matrizes`
GROUP BY `matrize_id`, `depositos`,`retiros` WITH ROLLUP) T1
WHERE (depositos IS NULL AND retiros IS NULL) 
OR (depositos IS NOT NULL AND retiros IS NOT NULL)
 

Ahora me marca este error:
#1146 - Table 'test.op_matrizes' doesn't exist

[gnzsoloyo]

Si te fijas, la base "test" es de mi servidor, porque tuve que hacer una prueba en él para obtener este resultado.
En cuanto a la explicación, te la debo para más tarde (horario de trabajo), porque no es corta.

[juan_aalberto]

ok muchas gracias
entonces test se refiere a tu base de datos?, esta consulta también la puedo usar cuando tengo números decimales verdad?
y de lo de la explicación no hay problema después cuando tengas mas tiempo me explicas

[gnzsoloyo]

Las tablas que usas no parecen tener declarados tipos FLOAT, o DECIMAL, pero en esencia es indiferente, porque el agrupamiento no impacta sobre eso, sino sobre las FK de las tablas.
Que sumes enteros o decimales es completamente irrelevante para la consulta. Simplemente sumará o restará lo que corresponda.

[gnzsoloyo]

El tema del uso del GROUP BY ... WITH ROLLUP, es algo que puedes ver en el manual de referencia, donde hay una muy buena y detallada explicación (http://dev.mysql.com/doc/refman/5.0/...modifiers.html).
En cuanto al uso de los IFs que hago, lo que hice fue primero ver cuál era el resultado y ver qué condiciones podría usar para hacer que la tabla devuelta pudiese incluir textos donde salieran los NULL, de modo de generar un especie de reporte.
Si eliminas toda la consulta mayor y usas sólo la subconsulta, puedes ver cómo es el conjunto devuelto en realidad, y de ese modo ver cómo y por qué puse los IFs de ese modo.
No hay mucho más que eso.

El WITH ROLLUP, si bien no es un estándar del SQL en toda regla, es una clausula que también existe en otros DBMS.