Adding a SUM Column to a Query

Question

I'm trying to do a query that returns the information of a purchase bill, but also there's another table that has detail of the bill, but I need to add that total to the other query, but I can't add the column, because it says that I need to have in the group by the same objects that in the select, and I tried it this way, but it says that it returns more than one value. Help please! :D

SELECT fc.fecha_factura_compra AS fecha, fc.id_factura AS no_factura, fc.serie,
       o.abreviatura + CAST(p.corr_id AS VARCHAR(255)) AS codigo_trupp, 
       p.nombre, fc.dias_credito, fc.tipo_cambio, 
       (SELECT SUM(dc.peso_neto * dc.precio_unitario) 
        FROM   detalle_compra AS dc 
        GROUP  BY dc.id_proveedor, 
                  dc.id_factura, 
                  dc.serie) AS total 
FROM   factura_compra AS fc, 
       origen AS o, 
       proveedor AS p, 
       detalle_compra AS dc 
WHERE  fc.id_proveedor = p.id_proveedor 
       AND dc.id_proveedor = p.id_proveedor 
       AND dc.id_factura = fc.id_factura 
       AND p.id_origen = o.id_origen 
       AND dc.serie = fc.serie 
       AND dc.id_factura = fc.id_factura 
       AND dc.id_proveedor = fc.id_proveedor 
       AND fc.activo_inactivo = 'true' 
       AND fc.anulada = 'false'

Answer 1

The construction with the subquery is extremely slow and to be avoided. A better solution is to write the query like below.

To add a total to a query you need to group by over all the other fields in your result set.

SELECT fc.fecha_factura_compra AS fecha
     , fc.id_factura AS no_factura
     , fc.serie
     , o.abreviatura + CAST(p.corr_id AS VARCHAR(255)) AS codigo_trupp 
     , p.nombre
     , fc.dias_credito
     , fc.tipo_cambio
     , SUM(dc.peso_neto * dc.precio_unitario) AS total 
FROM   factura_compra AS fc

JOIN   proveedor AS p
  ON   fc.id_proveedor = p.id_proveedor 

JOIN   origen AS o
  ON   p.id_origen = o.id_origen 

JOIN   detalle_compra AS dc 
  ON   dc.id_factura = fc.id_factura 
 AND   dc.serie = fc.serie 
 AND   dc.id_factura = fc.id_factura 
 AND   dc.id_proveedor = fc.id_proveedor 
 AND   dc.id_proveedor = p.id_proveedor 

WHERE  fc.activo_inactivo = 'true' 
  AND fc.anulada = 'false'

GROUP BY fc.fecha_factura_compra
      , fc.id_factura 
      , fc.serie
      , o.abreviatura + CAST(p.corr_id AS VARCHAR(255)) 
      , p.nombre
      , fc.dias_credito
      , fc.tipo_cambio