Home / Questions / 213689
Accepted
Juan
Asked: 2020-11-15 09:58:41 +0800 CST 2020-11-15 09:58:41 +0800 CST

Get error data in a mysql query with the condition that it only shows errors that cover 80% of total errors

  • 772

I have the following doubt, although I already did it by taking the data and processing it with php, I wanted to know if I can bring the information from the base in the following way:

Based on this query I get a list of errors with their respective impacts (total of those errors):

SELECT mtg.idTemaGestion, mtg.detTemaGestion,
count(mtg.idTemaGestion) as total
from mac_gestion mg
inner join mac360_mac_gestion mmg on mg.idMacGestion = mmg.idMacGestion_FK
inner join mac360 m on m.idMac360 = mmg.idMac360_FK
inner join mac_tipogestion tg on tg.idTipoGestion = mg.idTipoGestion_FK
inner join datos_nomina dn on dn.idDatosNomina = m.idDatosNominaRecibe_FK
inner join site si on si.idSite = dn.idSite_FK
inner join mac_temagestion mtg on mtg.idTemaGestion = mg.idTemaGestion_FK
where mg.error = 'si'
and si.idSIte in (2)
and tg.idTipoGestion in (1)
group by mtg.idTemaGestion
order by total desc;

enter image description here

After this, what I need is to put together something like a pareto where it only shows me the errors that cover 80%.

  • My total errors is 189
  • As I show in the image, taking out the % of impact and then adding them, I am armed with the group of data that I have to show since those cover 80% of the total errors, the others are not necessary since the impact is little or null.

enter image description here

Now, is it possible to return that information by query directly and not have to process the data later? With stored procedures? Can you declare variables within the query and validate from there? They guide me to know what I have to look for in order to answer this question that I have, thank you very much.

SOLUTION BASED ON THE ANSWER:

SELECT *, round((c.acumulado / total), 2) as pareto
from (
SELECT titulo, cantidad,
(select sum(cantidad) from errores) as total,
@acum:= @acum + cantidad as acumulado
from errores
JOIN (SELECT @acum:= 0) r
order by cantidad desc
) c
WHERE C.ACUMULADO/Total <= 0.80

enter image description here

mysql

1 Answers

  1. Best Answer

    Of course you can, you need two things:

    • The sum total of the amounts
    • And a cumulative for each row from a descending ranking

    You can handle the total by means of a subquery, the accumulated eventually as well or by using a variable.

    The following is a proof of concept:

    create table Errores (
    Titulo varchar(100), 
    Cantidad INT
)

insert into Errores (Titulo, Cantidad)
VALUES  ('Error A', 15), 
        ('Error B', 85), 
        ('Error C', 25), 
        ('Error D', 64), 
        ('Error E', 10) 

SELECT *,
    C.ACUMULADO/Total
    FROM (SELECT   t.Titulo,
                   t.Cantidad,
                   @acum:= @acum+ t.Cantidad AS ACUMULADO,
                   tot.Total   
                   FROM Errores t
                   JOIN (SELECT @acum:= 0) r
                   JOIN (SELECT SUM(Cantidad) as 'Total' FROM Errores) tot
                   ORDER BY t.Cantidad desc
          ) C
          WHERE C.ACUMULADO/Total <= 0.8
          ORDER BY Cantidad desc

drop table Errores

    Result (We filter until the maximum value less than 80%):

    ╔═══╦═════════╦══════════╦═══════════╦═══════╦═══════════════════╗
║ # ║ Titulo  ║ Cantidad ║ ACUMULADO ║ Total ║ C.ACUMULADO/Total ║
╠═══╬═════════╬══════════╬═══════════╬═══════╬═══════════════════╣
║ 1 ║ Error B ║ 85       ║ 85        ║ 199   ║ 0,42713567839196  ║
╠═══╬═════════╬══════════╬═══════════╬═══════╬═══════════════════╣
║ 2 ║ Error D ║ 64       ║ 149       ║ 199   ║ 0,748743718592965 ║
╚═══╩═════════╩══════════╩═══════════╩═══════╩═══════════════════╝

    In this example, the table Errorescomes to represent your query, it may be convenient for you to enter it previously in a temporary table since you will use it again in a subquery, depending on the version of MySQL you could even use a CTE .

    If what you were looking for is to reach the first row that exceeds 80%, you could solve it in the following way:

    SELECT *    
    FROM (SELECT   t.Titulo,
                   t.Cantidad,
                   (CASE WHEN @acum/tot.Total < 0.8 THEN 0 ELSE 1 END) as 'Mayor80',
                   (@acum := @acum + t.Cantidad)/tot.Total as 'Porcentaje'
                   FROM Errores t
                   JOIN (SELECT @acum := 0) r
                   JOIN (SELECT SUM(Cantidad) as 'Total' FROM Errores) tot
                   ORDER BY t.Cantidad desc
          ) C
          WHERE Mayor80 = 0
          ORDER BY Cantidad desc

    Demo: sqlfiddle

    • 1