SQL - Bring the name of the city with the highest number of sales

Asked

Viewed 475 times

2

What would be the Mysql query to bring the city name with the highest number of sales?

CREATE TABLE `clientes` (
    `Codigo` INT(11) NULL DEFAULT NULL,
    `Nome` VARCHAR(50) NULL DEFAULT NULL,
    `Cidade` VARCHAR(50) NULL DEFAULT NULL
)

CREATE TABLE `vendas` (
    `Codigo_Venda` INT(11) NULL DEFAULT NULL,
    `CodigoCliente` INT(11) NULL DEFAULT NULL,
    `ValorVenda` DOUBLE NULL DEFAULT NULL
)

I tried this query, but it can bring wrong values:

SELECT C.Cidade, MAX(V.ValorVenda)
FROM Clientes C INNER JOIN vendas V ON C.Codigo = V.Codigo_Venda
GROUP BY C.cidade

I tried to put together max(sum(campo)) but mysql is not accepting

sql
  • 1

    MAX returns the highest value of a field. Mysql has not left MAX(SUM()) because it cannot solve nested aggregations. Let’s think a little about your exercise. Abstracting the database, how would you find out which city had MORE SALES? (sales quantity != greater added value of sales)

  • 1

    Another point: your JOIN is comparing customer code with sales code, it should be CodigoCliente.

2 answers

2


If you want to bring the name of the city where you got the most sales, based on the sum of the total values, regardless of the quantity of sales, you can use the function SUM:

SELECT C.Cidade, SUM(V.ValorVenda)
FROM Clientes C INNER JOIN vendas V ON C.Codigo = V.Codigo_Venda
GROUP BY C.cidade
ORDER BY 2 DESC
LIMIT 1

In case you want to bring the name of the city that got most sales in quantity, and not the sum of the values, you can use the function COUNT:

SELECT C.Cidade, COUNT(*)
FROM Clientes C INNER JOIN vendas V ON C.Codigo = V.Codigo_Venda
GROUP BY C.cidade
ORDER BY 2 DESC
LIMIT 1

  • But in case I just want to bring the name of the city with more sales, can not come the column of Count or any other column, just the name of the city. When I remove these columns at the beginning of select my query goes wrong and I’m not able to put it at the bottom so it works. Thank you very much!

  • Well, I don’t see why it would impact whether or not to bring the Count column, but it won’t be possible to delete it and get the query you want. What you can do is put the query in parentheses and select the city name without Count, but still doing your query. Example:

  • You will be disfigured because you are in comment, but try this: "SELECT City FROM (SELECT C.City, COUNT(*) FROM Customers C INNER JOIN sales V ON C.Code = V.Code GROUP BY C.city ORDER BY 2 DESC LIMIT 1) AS City;"

  • Now I understood the scheme of bringing only the name of the city using a select on the outside. Very grateful to all!

  • If you have solved your problem, please mark the answer as accepted so that the question is not open. Hug.

1

making sum in sales value will bring the one that sold the most in value, bring the one that has the most sales would be with Count 

SELECT C.Cidade, count(1) 
    FROM Clientes
 C INNER JOIN vendas V ON C.Codigo = V.Codigo_Venda
 GROUP BY C.cidade

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.