Doubt to bring mysql data

Asked

Viewed 90 times

2

I have the following question. Consider that I have the following sales data:

Two former states: São Paulo (Atibaia, São Paulo and Campinas) and Paraná (Londrina, Curitiba, Cascavel).

| cidade    | estado | vendas |
|-----------|--------|--------|
| Cascavel  | PR     | 10     |
| Curitiba  | PR     | 8      |
| Londrina  | PR     | 3      |
| Campinas  | SP     | 3      |
| São Paulo | SP     | 5      |
| Atibaia   | SP     | 3      |

I need to bring the cities of their respective states that have the highest number of sales.

I made this command: to make a Count in the city brings the sum, but now I need to separate the cities. in this case would have to bring São Paulo and Cascavel.

 select
  count(d_clientes.cidade_nome), f_pedidos.cliente_sk, d_clientes.cliente_sk, d_clientes.cidade_nome, d_clientes.estado_nome, d_clientes.estado_sigla 
 from
 f_pedidos 
 inner join d_clientes on f_pedidos.cliente_sk = d_clientes.cliente_sk  
group by d_clientes.cidade_nome
mysql sql

  • lol n understood nothing the statement , edita ai rsss - type put your db structure and exactly as you want to order.

  • I think you can use SQL MAX

  • @Jasarorion he understands well of SQL, top

1 answer

3

We can break this problem down into two parts. First we need to count the amount of sales by city and state:

SELECT cidade, estado, count(*) FROM vendas
GROUP BY cidade, estado
ORDER BY estado, count(*) DESC;

With that we have the summary already ordered. 

| cidade    | estado | count(*) |
|-----------|--------|----------|
| Cascavel  | PR     | 10       |
| Curitiba  | PR     | 8        |
| Londrina  | PR     | 3        |
| São Paulo | SP     | 5        |
| Campinas  | SP     | 3        |
| Atibaia   | SP     | 3        |

And how to find the city that sold the most per state?

For that we need to include a ranking of the cities that sold the most by state, something like this:

| cidade    | estado | count(*) | ranking 
|-----------|--------|----------|---------
| Cascavel  | PR     | 10       | 1
| Curitiba  | PR     | 8        | 2
| Londrina  | PR     | 3        | 3
| São Paulo | SP     | 5        | 1
| Campinas  | SP     | 3        | 2
| Atibaia   | SP     | 3        | 3

This can be done very simply with a window Function. Putting this into a subquery, the final job would be to make a filter where the ranking is equal to 1:

SELECT * FROM (
    SELECT cidade, estado, count(*), 
      row_number() over (partition by estado order by count(*) DESC) as rank
    FROM vendas
    GROUP BY cidade, estado
    ORDER BY estado, count(*) DESC;
) AS sub
WHERE rank = 1;

However, the Mysql does not support window functions :(

To achieve the same result, we need some adaptations in the part referring to ranking:

-- Não esqueça de definir as variáveis! Elas são importantes
SET @currcount = NULL, @currvalue = NULL;
SELECT cidade, estado, qtd_vendida FROM (
  SELECT *, 
      @currcount := IF(@currvalue = estado, @currcount + 1, 1) AS rank,
      @currvalue := estado AS bla
  FROM (
    SELECT cidade, estado, count(*) as qtd_vendida
    FROM vendas
    GROUP BY cidade, estado
    ORDER BY estado, count(*) DESC
  ) AS vendas
) AS ranking
WHERE rank = 1

See working on SQL Fiddle.

References:

Browser other questions tagged mysql sql

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