I have a table where I store sales. Home
There was a need to order sellers by the amount of sales.
In case of a tie the one who made the most recent sale gets first.
I can bring with the sql below, the seller and time of the sale, however I would like to already bring the sales sum of each seller and tie case already ordered by the time of the sale.
SELECT v.vendedor, v.data_venda , v.hora_venda
FROM vendas v
ORDER BY v.data_venda desc, v.hora_venda desc
Any ideas?
You can use the COUNT () function to get the total of records and the MAX () to get the highest time and date of the 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 highest date and time for each seller.
COUNT will bring total sales, as we are using GROUP BY by name.
I think the example is not functional, but you have to have a sense of logic to follow.
I think this will solve, I have not tested 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
Without looking at the structure of your table I thought of the query below.
It makes a% of Sales% and groups using COUNT(ID) per salesperson.
That way you will have the sales amount for each seller.
In this case the Dates can not be placed in the query to display, as they may be different and this interferes with GROUP BY .
SELECT
COUNT(ID) AS TOTAL_VENDA,
VENDEDOR
FROM
VENDAS
GROUP BY
VENDEDOR
ORDER BY
HORA_VENDA, DATA_VENDA
DESC