Problem with query in sql

In SQL queries with added functions, as in your case the group by, both the SELECT and the GROUP BY must have the same fields:

SELECT ventas.id_com, comprador.nombre, (SELECT SUM(ventas.Cantidad) 
             from ventas 
             WHERE comprador.id_com = ventas.id_com) as total_ventas
from comprador, ventas where comprador.id_com = ventas.id_com 
group by ventas.id_com, comprador.nombre

More information regarding queries with GROUP BY here w3schools

Try this approach with the subselect in the FROM and then calling it in the main select. In the subselect you bring the sum and the id of the buyer affected by the sum. Then it shows the sum in the main select and joins the buyer of the subselect with the buyer of the main select:

SELECT ventas.id_com, comprador.nombre, totales.cantidad_total
from comprador, ventas,
 (  
    SELECT comprador.id_com as c2, SUM(ventas.Cantidad) as cantidad_total from ventas, comprador WHERE comprador.id_com = ventas.id_com GROUP BY comprador.id_com
 )  totales 

where comprador.id_com = ventas.id_com AND totales.c2 = comprador.id_com

I should do the trick, if it does not work, comment and see it =)

As you mentioned above, the group by has to have the same fields of the select, except for the field of the group function.

The problem you have is that you do not leave the group by because you are putting a subquery in a fields of the select, the group by would only work within that subquery.

If you want it to work for you as you have done it, you should put something like this:

  

SELECT sales.id_com, buyer.name, SUM (sales.quantity) as total_sales from buyer, sales where buyer.id_com =   sales.id_com group by ventas.id_com, comprador.name

I hope it is your help and it works for you.

There are 3 conceptual errors in that query, first of all, the group by must contain all the fields of the query that are not "calculated" (Say, added functions and sub-queries). Second, you are already making a join with the%% table% when you type ventas , so the query is unnecessary, to express it more clearly (at least in SQL Server) you should write it:

FROM 
comprador c
INNER JOIN --O LEFT JOIN si quieres que aparezca los compradores sin ventas.
ventas v
ON v.id_com = c.id_com

Finally, and given that the sub-query is unnecessary given the previously seen, the fields that we would need to project are: from comprador, ventas , c.nombre (can be c.id_com but even if they are equal I prefer that they belong to the same table that v.id_com ) and c.nombre (the result of the added function SUM(v.cantidad) TotalCantidadVentas as SUM ). Here, because it has an aggregate function, it is strictly necessary that the rest of the fields outside the aggregate function are in the definition of TotalCantidadVentas , that is:

GROUP BY c.id_com, c.nombre

Our query would finally be like:

SELECT c.id_com, c.nombre, SUM(v.cantidad) TotalCantidadVentas
FROM 
comprador c
INNER JOIN --O LEFT JOIN si quieres que aparezca los compradores sin ventas.
ventas v
ON v.id_com = c.id_com
--WHERE 
GROUP BY c.id_com, c.nombre
--Having 

In the Group By you can use expreciones with the slogan that all the fields of the must exist in the Group By.

In the WHERE you can use expressions with any "projected" field of the query. From the Sum we have or others such as AVG, MAS, MIN, COUNT and even sub-queries that only use the projected fields of the superior query in their internal conditions.