Adding a SUM Column to a Query

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 b开发者_StackOverflowill, 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'

---

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 

---

Instead of grouping by the columns, you should restrict the sub-select on the join columns and omit detalle_compra from the outer select:

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(peso_neto*precio_unitario)
          from detalle_compra
         where serie = fc.serie and
               id_factura = fc.id_factura and
               id_proveedor = fc.id_proveedor) as Total
  FROM factura_compra as fc,origen as o, proveedor as p
 WHERE fc.id_proveedor = p.id_proveedor and
       p.id_origen = o.id_origen and
       fc.activo_inactivo = 'true' and
       fc.anulada = 'false'

Side note: Use the BIT type to store booleans:

...
       fc.activo_inactivo = 1 and
       fc.anulada = 0