CODEWITHSUNDEEP
sql-server
angel
angel

Reputation: 4632

How to put the sum of all rows in each row

this is my query:

SELECT clientes.nombre as [NombreCliente], 
       venta.usuario  as [NombreVendedor], 
       SUM((listaventa.precio) ) as [FinalTotal], 
       venta.id as [IdVenta],
       venta.fecha as [Fecha], 
       idproducto as [clave],
       producto.descripcion ,  
       listaventa.precio as [preciounitario], 
       listaventa.cantidad,
       listaventa.total as  [PrecioTtoal] 
 FROM venta  
 JOIN clientes on venta.idcliente = clientes.id  
 JOIN listaventa on listaventa.idventa=venta.id  
 JOIN producto on listaventa .idproducto =producto.id  
WHERE venta.id ='36' 
GROUP BY clientes.nombre, venta.usuario, venta.id, venta.fecha, listaventa.idproducto, producto.descripcion,  listaventa.precio, listaventa.cantidad, listaventa.total

Problem is, I don't get is sum, as query is checking id for id, it never return a sum(listaventa.precio) it returns the same than cantidad*preciou (of every product but it never sum it). Else if i try it 

SELECT clientes.nombre as [NombreCliente], 
       venta.usuario  as [NombreVendedor], 
       SUM((listaventa.precio) ) as [FinalTotal], 
       venta.id as [IdVenta],
       venta.fecha as [Fecha], 
       idproducto as [clave],
       producto.descripcion ,  
       listaventa.precio as [preciounitario], 
       listaventa.cantidad,
       listaventa.total as  [PrecioTtoal] 
  FROM venta  
  JOIN clientes on venta.idcliente = clientes.id  
  JOIN listaventa on listaventa.idventa=venta.id  
  JOIN producto on listaventa .idproducto =producto.id  
 WHERE venta.id ='36' 
   AND venta.id IN (SELECT * 
                      FROM listaventa 
                     WHERE idventa = 36)

I get this error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Upvotes: 0

Views: 419

Answers (2)

hoodaticus
hoodaticus

Reputation: 3880

I think this is what you're looking for, if you're trying to print an order/invoice/receipt/packing slip:

select     clientes.nombre as [NombreCliente], venta.usuario as [NombreVendedor], 
           venta.fecha, venta.id as [IdVenta], idproducto as [clave], 
           producto.descripcion, listaventa.precio as [preciounitario], 
           SUM(listaventa.cantidad) as [cantidad], 
           SUM(listaventa.total) as [LineaTotal], 
           (SELECT SUM(listaventa.total) FROM listaventa WHERE idventa = '36') 
           as [VentaTotal]

from       venta 
inner join clientes on venta.idcliente = clientes.id 
inner join listaventa on listaventa.idventa = venta.id 
inner join producto on listaventa.idproducto = producto.id  

where      venta.id ='36'

group by   clientes.nombre, venta.usuario, venta.fecha, venta.id, 
           listaventa.idproducto, producto.descripcion, listaventa.precio

Upvotes: 1

JNK
JNK

Reputation: 65157

Remove listaventa.precio from your GROUP BY clause.

It should generate the correct SUM for that field if you allow it to aggregate.

Upvotes: 1

Related Questions