Doubt to bring data from mysql

Question

Doubt to bring data from mysql

Navigation

#1 by (3 votes)

2

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

Two 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 largest number of sales.

I made this command: to count in the city there brings the sum, but I need to separate the cities now. in that case he 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

asked by anonymous 11.09.2016 / 20:06

1 answer

Null attribute check in @ Html.DisplayNameFor Extract numbers from a list using Regex

score 3 · Answer 1

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

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

With this 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 do you find the city that sold the most by state?

For this 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 do a filter where the ranking equals 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, MySQL does not support window functions : (

To get to the same result, you need some adjustments in the ranking part:

-- 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 in SQL Fiddle .

References: