How to put the sum of all rows in each row

Question

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.

You may want to clarify what you are after since the query as written makes no sense. — JNK, Jul 22 '11 at 02:52
This doesn't make sense. There's no reason anyone would want to know SUM(lista.precio). El total de los preciounitarios no significa nada. — hoodaticus, Jul 22 '11 at 03:01
possible duplicate of [Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.](http://stackoverflow.com/questions/1904314/only-one-expression-can-be-specified-in-the-select-list-when-the-subquery-is-not) — OMG Ponies, Jul 22 '11 at 03:24
I don't think so, OMG. This is more like "How to return the sum of all rows in each row." — hoodaticus, Jul 22 '11 at 03:34

score 1 · Answer 1 · answered Jul 22 '11 at 02:41

1

Remove listaventa.precio from your GROUP BY clause.

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

answered Jul 22 '11 at 02:41

JNK

63,321
15
122
138

then i get "Column 'listaventa.idproducto' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." – angel Jul 22 '11 at 02:43
It shouldn't be in the `SELECT` list also. Remove it. How do you expect to get both the field and the aggregate of the field on the same row? – JNK Jul 22 '11 at 02:47

hoodaticus · Accepted Answer · 2011-07-22T03:33:09.547

1

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

edited Jul 22 '11 at 03:33

answered Jul 22 '11 at 03:19

hoodaticus

3,772
1
18
28

No, i really want to get how is the total of all the products, but i want this sum in all the rows (this is for a crystal report) – angel Jul 22 '11 at 03:25
Ah, say no more. I edited my answer by adding a VentaTotal column. – hoodaticus Jul 22 '11 at 03:31
Good. I may have forgotten to include that last column in the group by though, so just add it in if necessary. – hoodaticus Jul 22 '11 at 13:21

How to put the sum of all rows in each row

2 Answers2