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 already bring the information from the base as follows:
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;
After this, what I need is to build something like a pareto where it only shows me the errors that cover 80%.
- My total of errors is 189
- As I show in the image taking the% of impact and then adding them I am armed with the group of data that I have to show since they cover 80% of the total errors, the others are not necessary since the impact is little or null.
Now, is it possible to return that information by query directly and not have to process the data afterwards? 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 doubt I have, thank you very much.
BASANDOME SOLUTION IN 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