Count Mysql records

Asked

Viewed 1,170 times

2

I have a table where I store sales performed.
The need to order sellers by quantity of sales has arisen.
In case of a tie who made the most recent sale comes first.

I can bring with sql below, the seller and time of sale, but I would like to bring already the sum of sales of each seller and if tie already ordered by time of sale. 

SELECT v.vendedor, v.data_venda , v.hora_venda 
FROM vendas v
ORDER BY v.data_venda desc, v.hora_venda desc

Any idea ?

mysql

  • The sale value is in the same table?

  • 1

    The sale value is irrelevant, only the quantity matters.

  • 1

    Put the table structure Sales.

  • It only makes sense if you have more than one sale to a seller at the same time .

3 answers

5


You can use the function COUNT() to get the total records and function MAX() to get the highest time and date of sale.

SELECT 
    v.nome,
    (select MAX(vd.data_venda) from vendas vd where vd.nome = v.nome) as data,
    (select MAX(vh.hora_venda) from vendas vh where vh.nome = v.nome) as hora,
    COUNT(v.nome) as qtde_vendas
FROM 
    vendas v
GROUP BY
    v.nome, data, hora
ORDER BY 
    qtde_vendas desc, data desc, hora desc

Using the sub selects you will select the largest date and time of each seller. The COUNT will bring the total sales as we are using the GROUP BY by name.

I believe that the example is not functional, but to have a notion of the logic by which to follow.

  • 1

    Accurate. I took advantage and learned subquerys. Thank you !

  • 1

    @Pedro Camara Junior, helped a lot, it was a great response

0

Without looking at the structure of your table I thought of the query below. Makes a COUNT(ID) of Sales and groups using the GROUP BY per seller.

This way you will have the amount of sales for each seller. In this case the Dates cannot be placed in the query to display, since they can be different and this interferes with the GROUP BY.

SELECT
    COUNT(ID) AS TOTAL_VENDA,
    VENDEDOR
FROM
    VENDAS
GROUP BY
    VENDEDOR
ORDER BY 
    HORA_VENDA, DATA_VENDA 
DESC
  • 1

    SUM(ID)? Why SUM()?

  • IS COUNT(ID). I made a mistake, bro. I made a mistake. I can’t make a mistake ? Will you judge me ? Only God can judge me.

  • lol suspected it was COUNT()

0

I guess that solves it, I didn’t test it.

SELECT v.vendedor, v.data_venda , v.hora_venda, tmp.quantidade 
FROM vendas v,
inner join (select vendedor, count(1) as quantidade
from vendas
group by vendedor) tmp
on tmp.vendedor = v.vendedor
ORDER BY v.data_venda desc, v.hora_venda desc, tmp.quantidade

Browser other questions tagged mysql

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