Logic for ABC curve

Question

I am developing a project and I would like to make a page only for statistical data, one of these data is the ABC Curve of customers, I mean, I want to return the customers who buy the most. I’ve been trying for days to think of a logic to solve this but I can’t get anything that isn’t "gambiarra".

Well, the system looks like this: the operator finalizes the sale and if any customer is attached, the system records its ID. In the database the sales table has the column "client_id".

The idea is to make a SELECT to go through this table and go counting how many sales a particular customer has. The problem is I can’t think of anything to fix it.

Answer 1

Your select would look like this:

SELECT COUNT(v.id) as qtd_vendas, v.client_id as id_cliente, c.nome as cliente
FROM tb_vendas v
  LEFT JOIN tb_cliente c ON c.id = v.client_id
GROUP BY id_cliente, cliente
ORDER BY qtd_vendas DESC;

From the moment you rename a column with the AS you can put in the GROUP BY and ORDER BY only the last names. I put this other adding the value of sales per customer also (taking into account that for each sale we have the value of the same) and ordering the maximum value of the sale, which is so:

SELECT COUNT(v.id) AS qtd_vendas,
       SUM(v.valor_venda) AS valor_venda,
       v.client_id as id_cliente, c.nome as cliente
FROM tb_vendas v
  LEFT JOIN tb_cliente c ON c.id = v.client_id
GROUP BY id_cliente, cliente
ORDER BY qtd_vendas DESC;

Explaining this last select:

• COUNT will count the amount of sales made per customer;
• SUM is to sum up the value of sales and present per customer.
• LEFT JOIN because you want to stress the amount of sales per customer or be the priority is customers but who are on the sales chart.
• GROUP BY group the information by client and how I put the client name, also need to inform this field in group by.
• ORDER BY paid value DESC, orders the result at the maximum amount of purchases made for the lowest amount of purchases made per customer, already showing you which customer bought the most (remembering that here would have the value of which customer bought more times, if you want the customer who had the highest value in real purchases just exchange to sort by the field value_sale).

Answer 2

Your SQL query will need a grouping function, and it will look like something like this:

//seleciono os campos que necessito (nome e quantidade) da tabela de vendas
SELECT client_name, count(client_id) 'Qtde vendas' FROM vendas
//relaciono com a tabela de clientes para recuperar o nome do cliente
INNER JOIN clientes ON id = client_id
//especifico que quero os resultados agrupados pelo nome do cliente 
GROUP BY client_name
//e ordeno do maior para o maior
ORDER BY count(client_id) desc;