Advertisement
viktormorales

Obtener SALDO entre DOS TABLAS en UNA sentencia SQL

May 17th, 2012
681
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 1.92 KB | None | 0 0
  1. # Según consulta hecha en: http://foros.cristalab.com/obtener-saldo-entre-dos-tablas-en-una-sentencia-sql-php-mysql-t105748/#643660
  2.  
  3. # 1 - COBROS: SUMAR COLUMNA COBRO [RESULTADO ESPERADO: 330]
  4. > SELECT SUM(p.cobro) AS cobro FROM tabla1;
  5. > resultado obtenido: 330 [OK]
  6.  
  7. # 2 - VENTAS: MULTIPLICAR COLUMNAS CANTIDAD Y PRECIO_UNITARIO [RESULTADO ESPERADO: 830]
  8. > SELECT SUM(precio_unitario * cantidad) AS venta FROM tabla2
  9. > resultado obtenido: 830 [OK]
  10.  
  11. # 3.1 - SALDO: tabla2.venta - tabla1.cobro [RESULTADO ESPERADO: 500]
  12. > SELECT SUM( t2.precio_unitario * t2.cantidad ) - t1.cobro AS saldo
  13. > FROM  `tabla2` AS t2
  14. > JOIN  `tabla1s` AS t1
  15. > resultado obtenido: 2210.00 [ERROR]
  16.  
  17. # 3.2 - SALDO tabla2.venta - SUM(tabla2.cobro) [RESULTADO ESPERADO: 500]
  18. > SELECT SUM( t2.precio_unitario * t2.cantidad ) - SUM(t1.cobro) AS saldo
  19. > FROM  `tabla2` AS t2
  20. > JOIN  `tabla1s` AS t1
  21. > resultado obtenido: 840.00 [ERROR]
  22.  
  23. # 4 - ACERCAMIENTO
  24. > SELECT SUM( r1.cobro )
  25. > FROM (
  26. >
  27. > SELECT t1.cobro
  28. > FROM table1 AS t1
  29. > ) AS r1
  30. > UNION (
  31. >
  32. > SELECT SUM( t2.cantidad * t2.precio_unitario ) AS venta
  33. > FROM table2 AS t2
  34. > )
  35. > resultado obtenido: col1 = 330 | col2 = 830
  36.  
  37. # 5 - ERROR: #1222 - The used SELECT statements have a different NUMBER OF COLUMNS
  38. > SELECT SUM(r1.cobro) FROM (
  39. >    SELECT t1.cobro FROM table1 AS t1
  40. > ) AS r1 UNION (
  41. >    SELECT t2.cantidad, t2.precio_unitario FROM table2 AS t2
  42. > )
  43.  
  44. # 6 - ERROR: #1054 - UNKNOWN COLUMN 'ventas' IN 'field list'
  45. SELECT SUM(r1.cobro) - ventas FROM (
  46.     SELECT t1.cobro FROM table1 AS t1
  47. ) AS r1 UNION (
  48.     SELECT SUM(t2.cantidad * t2.precio_unitario) AS ventas FROM table2 AS t2
  49. )
  50.  
  51. # 7 - SOLUCION [RESULTADO ESPERADO: 500]
  52. > SELECT (t2.ventas - t1.cobros) AS saldo
  53. > FROM (
  54. >
  55. > SELECT SUM( DISTINCT cobro ) AS cobros
  56. > FROM table1
  57. > ) AS t1
  58. > CROSS JOIN (
  59. >
  60. > SELECT SUM( cantidad * precio_unitario ) AS ventas
  61. > FROM table2
  62. > ) AS t2
  63. > resultado obtenido: 500 [OK]
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement