I want to list the best clients by region of the northwind table. For this I did this
select ContactName,r.RegionID,sum(Quantity) as 'Cantidad comprada por el cliente' from Customers c
join Orders o on o.CustomerID = c.CustomerID
join [Order Details] od on od.OrderID = o.OrderID
join Employees e on e.EmployeeID = o.EmployeeID
join EmployeeTerritories et on et.EmployeeID = e.EmployeeID
join Territories t on t.TerritoryID = et.TerritoryID
join Region r on r.RegionID = t.RegionID
group by ContactName,r.RegionID
order by SUM(Quantity) desc
although I do not know how to select the regions and the different names and the maximum of the previous sum.
My exit would have to be
nombrecliente idregion cantidad comprada
xxxx 1 xxxxxxx
xxx 2 xxxxxxxx