Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- # Según consulta hecha en: http://foros.cristalab.com/obtener-saldo-entre-dos-tablas-en-una-sentencia-sql-php-mysql-t105748/#643660
- # 1 - COBROS: SUMAR COLUMNA COBRO [RESULTADO ESPERADO: 330]
- > SELECT SUM(p.cobro) AS cobro FROM tabla1;
- > resultado obtenido: 330 [OK]
- # 2 - VENTAS: MULTIPLICAR COLUMNAS CANTIDAD Y PRECIO_UNITARIO [RESULTADO ESPERADO: 830]
- > SELECT SUM(precio_unitario * cantidad) AS venta FROM tabla2
- > resultado obtenido: 830 [OK]
- # 3.1 - SALDO: tabla2.venta - tabla1.cobro [RESULTADO ESPERADO: 500]
- > SELECT SUM( t2.precio_unitario * t2.cantidad ) - t1.cobro AS saldo
- > FROM `tabla2` AS t2
- > JOIN `tabla1s` AS t1
- > resultado obtenido: 2210.00 [ERROR]
- # 3.2 - SALDO tabla2.venta - SUM(tabla2.cobro) [RESULTADO ESPERADO: 500]
- > SELECT SUM( t2.precio_unitario * t2.cantidad ) - SUM(t1.cobro) AS saldo
- > FROM `tabla2` AS t2
- > JOIN `tabla1s` AS t1
- > resultado obtenido: 840.00 [ERROR]
- # 4 - ACERCAMIENTO
- > SELECT SUM( r1.cobro )
- > FROM (
- >
- > SELECT t1.cobro
- > FROM table1 AS t1
- > ) AS r1
- > UNION (
- >
- > SELECT SUM( t2.cantidad * t2.precio_unitario ) AS venta
- > FROM table2 AS t2
- > )
- > resultado obtenido: col1 = 330 | col2 = 830
- # 5 - ERROR: #1222 - The used SELECT statements have a different NUMBER OF COLUMNS
- > SELECT SUM(r1.cobro) FROM (
- > SELECT t1.cobro FROM table1 AS t1
- > ) AS r1 UNION (
- > SELECT t2.cantidad, t2.precio_unitario FROM table2 AS t2
- > )
- # 6 - ERROR: #1054 - UNKNOWN COLUMN 'ventas' IN 'field list'
- SELECT SUM(r1.cobro) - ventas FROM (
- SELECT t1.cobro FROM table1 AS t1
- ) AS r1 UNION (
- SELECT SUM(t2.cantidad * t2.precio_unitario) AS ventas FROM table2 AS t2
- )
- # 7 - SOLUCION [RESULTADO ESPERADO: 500]
- > SELECT (t2.ventas - t1.cobros) AS saldo
- > FROM (
- >
- > SELECT SUM( DISTINCT cobro ) AS cobros
- > FROM table1
- > ) AS t1
- > CROSS JOIN (
- >
- > SELECT SUM( cantidad * precio_unitario ) AS ventas
- > FROM table2
- > ) AS t2
- > resultado obtenido: 500 [OK]
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement