Sumar o restar campos generados

[sencev]

Buenas gente, estoy haciendo una query y me tope con este problema:

Tengo una tabla de productos, una de ordenes y una de pagos.

La relacion es 1 orden -> varios productos, con varios pagos (mini pagos) POR ORDEN.

Código sql:

SELECT ordenes.id
     , ( SUM( productos.valor ) - productos.iva_total ) AS total
     , ( SELECT SUM( pagos.importe_total ) FROM pagos WHERE ordenes_id = ordenes.id GROUP BY ordenes_id ) AS pagado
  FROM ordenes
INNER JOIN productos,, con su tabla "ordenes_productos", etc
GROUP BY ordenes.id
 

Esta query es parte de un listado, y como se le aplican filtros que modifican el SQL dinamicamente, necesito tener que hacer esto por SQL si o si..

Entonces:
Como poria obtener el resultado de total - pagado? para hacer una condicion por ejemlo

Código sql:

WHERE ( total - pagado ) > 0 #Que traiga solo las ordenes que tienen saldo pendiente
 

Intente haciendo la cuenta ahi mismo, asi:

Código sql:

(( SUM( productos.valor ) - productos.iva_total ) - ( SELECT SUM( pagos.importe_total ) FROM pagos WHERE ordenes_id = ordenes.id GROUP BY ordenes_id )) AS diferencia
 

Pero me trae el campo null, por lo que deduzco que no es capaz de hacer esa operacion..

Ya intente todo lo que se me ocurrio pero nada funciono jaja..
alguna idea ??

Saludos

[quimfv]

Primero de todo creo que te falta una tabla, deberias tener

ordenes
idOrden
fecha
...
//Cabecera de la orden, datos generales, fecha cliente... etc



DetalleOrden
idDetalle
idOrden
idProducto
cantidad
precio
IVA


//Lineas de la orden producto cantidad y precio aplicado

Productos
idProducto
Nombre
Precio
IVA%

//Catalogo de productos con el nombre y el precio actual


Pagos
idPago
idOrden
Fecha
importe

//Pagos, identificador de la orden a la que corresponde, fecha y importe

Con esa estructura podriamos funcionar.

Sobre lo que nos pasas NO entiendo esta operación

Código sql:

...
( SUM( productos.valor ) - productos.iva_total ) AS total
...
 

si el campo iva_total esta en productos luego deberia ser

Código sql:

...
 SUM( productos.valor - productos.iva_total ) AS total
...
 

Si tienes el IVA includo en valor, luego no tines que sacarlo para comparar con pagos puesto que los pagos seran tambien con iva incluido... no?

Luego para ver la ordenes con saldo pendiente, divide y venceras...

Código sql:

SELECT ordenes_id, SUM(pagos.importe_total) 
FROM pagos 
GROUP BY ordenes_id
 

Esto nos da lo pagado de cada orden...

Código sql:

SELECT productos.ordenes_id, 
         SUM( productos.valor - productos.iva_total ) AS totalSinIva,
         SUM( productos.valor ) AS totalConIva
FROM productos
GROUP BY productos.ordenes_id
 

Esto nos da el total de la orden (con o sin IVA, tu sabras)

Ahora solo falta mezclarlas...

Código SQL:

SELECT ValorOrden.Orden, 
            (ValorOrden.totalConIva-IFNULL(PagosOrden.Pagado,0)) AS Pendiente
FROM (SELECT productos.ordenes_id AS Orden, 
                  SUM( productos.valor - productos.iva_total ) AS totalSinIva,
                  SUM( productos.valor ) AS totalConIva
               FROM productos
               GROUP BY productos.ordenes_id) AS ValorOrden
           LEFT JOIN
                (SELECT ordenes_id AS Orden, SUM(pagos.importe_total) AS Pagado 
                  FROM pagos 
                GROUP BY ordenes_id) AS PagosOrden
           ON ValorOrden.Orden=PagosOrden.Orden
WHERE (ValorOrden.totalConIva-IFNULL(PagosOrden.Pagado,0))>0;
 

No lo he probado pero seria lago así.

Quim

[sencev]

Hola Quim, antes que nada muchas gracias por tu respuesta y tu tiempo.

Si bien el modelo de productos y ordenes lo puse de ejemplo para que se entendiese el problema, es mas complejo que eso.

en realidad no tengo productos, ni ordenes, sino caballos, embarques, liquidaciones, y alguans tablas mas relacionadas..

Gracias a tu ejemplo, entendi como hacer los Selects adentro de un LEFT, porque antes los hacia mal, y no me dejaba relacionar las tablas cuando hacia LEFT JOIN ( Select.. where id = TABLADEAFUERA.id ).. me decia que no existia TABLADEAFUERA, y tenia razon..

En fin, me volvi un poco loco pero lo pude resolver, Tenia que se de esta forma, porque usa la misma query (simplificada) para hacer la paginacion..

En un punto usando el select adentro del LEFT, pero para el otro ya era mucho quilombo porque esta todo agrupado, pero pude usar Having, la query quedo asi:

Código sql:

SELECT l.id
       , cl.razon_social AS cliente_liquidador
       , l.embarque_id
       , Date_Format( e.fecha_salida, '%d/%m/%Y' ) AS fecha_salida
       , p.nombre AS destino
       , COUNT( c.id ) AS cantidad_caballos
       , Group_Concat( DISTINCT r.nombre ORDER BY r.nombre Separator ', '  ) AS razas
       , SUM( c.valor ) AS valor_caballos
       , ( SUM( c.valor ) - ( ( SUM( c.valor ) * 9.0909 ) / 100 ) - l.gastos_total - l.iva_total ) AS total
       , pagos.pagado
       , ( ( SUM( c.valor ) - ( ( SUM( c.valor ) * 9.0909 ) / 100 ) - l.gastos_total - l.iva_total ) - pagos.pagado ) AS saldo
      FROM liquidaciones l
INNER JOIN embarques e
        ON e.id = l.embarque_id
INNER JOIN clientes cl
        ON cl.id = l.cliente_liquidador_id
INNER JOIN paises p
        ON p.id = e.destino_aereo_id
 LEFT JOIN ( SELECT ( SUM( importe_total ) - SUM( retencion_ganancia ) ) AS pagado, liquidacion_id FROM liquidaciones_pagos GROUP BY liquidacion_id ) AS pagos
        ON pagos.liquidacion_id = l.id
 LEFT JOIN liquidaciones_caballos lc
        ON lc.liquidador_id = l.id
 LEFT JOIN caballos c
        ON c.id = lc.caballo_id
 LEFT JOIN razas r
        ON r.id = c.raza_id
  GROUP BY l.id
    HAVING saldo > 0 # Con saldo pendiente
  # HAVING ( IsNull( saldo ) OR saldo <= 0 ) - Sin saldo pendiente
 

No me gusta mucho que se repita el ( Sum( c.valor ) - ( ( Sum( c.valor )... pero buen, ya fue xD

Ah, ahora que veo bien me parece que el Having me hubiese servido desde el primer momento,, lol

Saludos