php – Help with creating ABC curve in MySQL

Question:

I set up a select to know which products were sold in a certain period and the sales value of these products.

But I need to build an ABC curve with the result of this select.

Could someone help me please.

My select looks like this:

SELECT DISTINCT d.cat_id prod, p.id, p.nome id_prod, SUM( d.qtd ) AS qtd_prod, SUM( d.valor_total ) AS total_prod
FROM lc_controle c
INNER JOIN lc_detalhe d ON d.controle_id = c.id
INNER JOIN  `lc_cat` p ON p.id = d.cat_id
WHERE d.tipo =  '0'
AND c.dtConcat
BETWEEN  '2018-01-01'
AND  '2018-03-31'
GROUP BY d.cat_id
ORDER BY qtd_prod DESC

With this select I have the following result:

prod    id  id_prod qtd_prod    total_prod
43  43  JVP-101A - BOMBA DE CIRCULAÇÃO 3.000 L/H 110V   353 12983.24
41  41  JVP-100A - BOMBA DE CIRCULAÇÃO 2.500 L/H 110V   352 10704.09
148 148 JVP-110A - BOMBA DE CIRCULAÇÃO 2.000 L/H 110V   242 7495.04
78  78  HJS-20 CARVÃO ATIVADO 500GR     208 1497.87
252 252 JVP-120 - BOMBA DE CIRCULAÇÃO 3.000 L/H 110V    181 5753.87

Now I need to add the sales amounts that are in the lc_control table to the total column for the same period and then divide the sale amount of each item by this total to be able to calculate the percentage that the item has on the total sale and then define if it is it a, b or c

How is it possible to do this?

Answer:

select
d2.codprod,d2.vlr,d2.qtd,perc ,d2.pracu,
case when d2.pracu <= 80 then 'A'
when d2.pracu <= 95 then 'B'
ELSE 'C' END CLASSE



from (
select
d1.codprod,d1.vlr,d1.qtd,d1.totalgeral,d1.perc,sum(perc) over(order by d1.perc desc) pracu
from(
select
d.codprod,d.qtd,
d.vlr, sum(d.vlr) over() totalgeral, cast(d.vlr  as decimal(15,2)) / cast(sum(d.vlr) over() as decimal(15,3)) *100 perc

from(

select i.codprod,sum(precototalcomdesc) as vlr,sum(quantidade) as qtd from iten_vendatemp
 AS I INNER JOIN PRODUTOS AS P ON ( P.CODPROD = I.CODPROD) inner join vendas as
 v on ( v.codigovenda = i.codvenda)
 inner join formadepagamento as f on(f.id = v.idformpag) where  I.situacao = 2
 and f.NAOTOTALIZARRELATORIOVENDAS = false group by codprod order by  vlr desc) d)d1)d2
Scroll to Top